Thread: Search for incorrect pattern

I have used charindex and patindex but I'm wondering if anyone has a good way to deal with the problem I'm trying to figure out.

If you look at the following data:

Code:

CREATE TABLE #MYTEMPTABLE (COL1 INT PRIMARY KEY, COL2 VARCHAR(30))

INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (1, 'YES PCA')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (2, ' YES PCA')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (3, 'NO PCA')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (4, ' NO PCA')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (5, 'PCA YES')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (6, 'PCA NO')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (7, ' YES ')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (8, ' NO')
INSERT INTO #MYTEMPTABLE (COL1, COL2) VALUES (9, ' Was PCA APPROVED')

SELECT * FROM #MYTEMPTABLE

The values returned are:

Code:

1    YES PCA
2     YES PCA
3    NO PCA
4     NO PCA
5    PCA YES
6    PCA NO
7     YES 
8     NO
9     Was PCA APPROVED

The values for ID's (Col1) 1 and 3 are the "correct" patterns I'm looking for.
Given this example, if I want to search for anything where Col2 is not in
('YES PCA', 'NO PCA'), I could write a query stating:

SELECT *
FROM #MYTEMPTABLE
WHERE (LEFT(COL2,7)<>'YES PCA' OR
LEFT(COL2,6)<>'NO PCA')

but the problem is that it just returns all 9 rows when I would only want to see those rows that "weren't correct"

I'm looking to say something like:

Select case when (LEFT(COL2,7)<>'YES PCA' OR
LEFT(COL2,6)<>'NO PCA') then 1 else 0 end as 'NEEDS TO BE FIXED'

FROM #MYTEMPTABLE

Hopefully this is making sense...the columns where "PCA" doesn't show up at all or if "NO" or "YES" don't show up in COL2 kind of baffle me.

Thanks!
Craig

EDIT: The results returned look trimmed in my POST...obviously records 2,4,7,8 and 9 have leading spaces in the result set

Not sure I see why you are using the LEFT function here. Would not this query return what you are looking for?

Code:

select * from #myTempTable
where COL2 NOT IN ('YES PCA', 'NO PCA')

In that case, try:

Code:

 select *
 from #myTempTable
 Where col2 not like ('YES PCA %') 
   and col2 not like ('NO PCA %') 
   and col2 not in ('YES PCA', 'NO PCA')

The slightly more efficient

Code:

   SELECT *
FROM #MYTEMPTABLE
WHERE NOT (LEFT(COL2,7)='YES PCA' OR
LEFT(COL2,6)='NO PCA')

would work with your example data, but would miss 'YES PCASSISTANT'

I think the real issue here is that you are treating the contents of a column as if it may contain more than one kind of a value. While it's possible to write awkward searches even in such cases, it's a violation of relational data normalization to treat a column as non-atomic ("single valued"). You are searching for pieces of a value. Perhaps you are forced to deal with the data you are given, but if you are designing a database, you should try to avoid this by having columns for PCA (Yes or No) and additional qualifiers. That's the reason for the normalization rules, to permit simple queries to retrieve the data you want.

Indeed Don;
Generally, if you find it hard to write a query or need to handle lots of 'exceptions' in complicated Where clauses, its a good indication that your DB Design is flawed. Ideally, working with YES/NO type values, one would use a bit field; and if there are different 'flavors' of Yes/No, then additional fields would be added (to basically say the same thing as Don).

Also, one should be careful to not allow values such as <blank>Yes to be stored in the first place.

Craig;
Yup, I remember that fondly(?). Figured it was another case of 'inherited DB design'.

Could you update the table to strip out the leading spaces on some 'maintenance cycle', or would that bollocks up a process someplace else?