kfschaefer

I am updating a table from text document - I need to limit the data to only those records that met a certain format criteria. (####-###)

My field for ProjectNum currently contains mixed data -

example:

2787
116202
119002
140302
154402
155902
157602
4714001
5157001
5739001
6249001
6335001
00000-000
00000-001
00001-001
00001-002
00001-005
00001-011
00002-001
00002-002
CADFileList
CADFileList
CINCINNAT
filelist
filelist
NULL
nzweob
Shortcut (2) to Forecast
Shortcut to Forecast
SLCT1F
STARBUCKS
TEST
TESTX



The only data I want in my table is the correct ProjectNum = 00002-002.

How do I insure that my data meets this criteria (####-###)

Here is my current query - feel free to make changes accordingly.

Karen

SELECT Q1.ProjectNum,
CURRENT_TIMESTAMP AS DateCreated,
'Import' AS UserCreated,
Q1.DateModified,
'Import' AS UserModified
FROM
(
SELECT Cast(Col004 as varchar(9)) AS ProjectNum, Cast(Col008 as datetime) AS DateModified
FROM CADFileList
WHERE (Col004 IS NOT NULL) OR
(Col004 LIKE '[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9]%)') or Len(Col004)=9
) Q1
LEFT OUTER JOIN ProjectDemand ON Q1.ProjectNum = ProjectDemand.ProjectNumber
WHERE (ProjectDemand.ProjectNumber IS NULL) OR
(ProjectNum LIKE N'[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9]%)' or Len(ProjectNum)=9)

codenode

Instead of LIKE you need REGEXP. And to get a pattern exactly ####-###
and nothing more the regex pattern would be:
^[0-9][0-9][0-9][0-9]\-[0-9][0-9][0-9]$