Question Finding strings

[scottsanpedro]

Hi all. I have a problem finding strings that do not exist in a column in a database. I am going to use linq but its more the 'way' of doing I am concerned with.

I have strings like;
MEAL
GPST
EXLEG
EXLG ...etc

I need to find records that does not have this list of strings in, so for example DBML
BUT
sometimes I can have the string MEALDBML, or more concatenated. So this is a known string of MEAL and unknown string of DBML. I would then want to see that record.
I do not know the unknown strings up front, so its a case of feeding in the strings I know are ok, and finding one that are outside that list.
Any help will be gratefully accepted.
Scott

 

[jmcilhinney]

Your description is not very clear. Are you saying that you want to get all the records where the value in a particular column is not equal to any of those in a specific list?

 

[scottsanpedro]

ok. I have a uniqueID and text column that could have any one of the following;
1 MEAL
2 MEALGSPT
3 MEALDBML

The idea is that the data coming in is freehand and certain codes (all 4 letters which I think may be the key) are valid and known, or not known. I need to pull out the unknown codes.
So as above.
MEAL is known
GSPT is Known
DBML is not known.

Each record has an associated unique key. So I would return 3 and the unknown value.

If i do a normal (SQL) Where remarks NOT LIKE '%MEAL%' AND remarks NOT LIKE '%GSPT%' Then I will miss the DBML as its on the same line as the 'MEAL'

I have done a long way round already by creating a new column and basically taking away all matches and I'm left with any that are unknown. I am doing this with a REPLACE(Remarks,'MEAL','')

I will either do this in .net or in SQL Server. I have happy either way.

Thanks for your time
Scott

 

[Menthos]

Assuming your 'test' field is only ever going to be a maximum of eight characters, off the top of my head I'm tempted to suggest creating a CTE with a UNION to split the first and second halves into a single column, then process the CTE with whatever logic you want.

Just an idea.

 

[scottsanpedro]

Not going to be limited to 8 chars.
I have left it as taking away the known codes and leaving unknowns by a replace. Seems primitive and long hand but it solves it.
I could array each set of 4 chars and then check it against a known list.
Bit of a tough one. Cheers for your comments. Scott