Limiting Data to specific Format
-
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)
-
If the Project Number will "always" contain this format, then you should be able to use the following within your nested SELECT (Q1)
WHERE SUBSTRING( Col004, 5, 1) = '-'
AND ISNUMERIC( SUBSTRING( Col004, 1, 4)
AND ISNUMERIC( SUBSTRING( Col004, 6, 3)
You can determine how many constraints you need, like ISNUMERIC.
I wasn't born stupid - I had to study.
-
I still get an error of Function not found when I parse the follwing query.
SELECT DISTINCT Q1.FilePath, Q1.ProjectNum, Q1.Filetype, Q1.DateModified
FROM (SELECT Col002 AS FilePath, Col004 AS ProjectNum, Col006 AS Filetype, Col008 AS DateModified
FROM CADFileList
WHERE (SUBSTRING(Col004, 5, 1) = '-') AND (ISNUMERIC(SUBSTRING(Col004, 1, 4)) AND (ISNUMERIC(SUBSTRING(Col004, 6, 3)))
Q1 LEFT OUTER JOIN
ImportFileLog ON Q1.ProjectNum + '.txt' = ImportFileLog.FileName
WHERE (ImportFileLog.FileName IS NULL) OR
(Q1.DateModified > ImportFileLog.DateImported)
-
To me it looks that when you have a column named ProjectNum and store there a wild mixture of strings, numbers, and combinations, you might need to revise your data model. I suspect you will be in trouble sooner or later with this structure anyway.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply