CODEWITHSUNDEEP
stringsql-server-2008filter
Guilherme Lopes
Guilherme Lopes

Reputation: 4760

How do I filter a SQL query by exact match to string?

I'm using MS SQL 2008 and I have a status field that comes like this:

"REF CNF PCNF REL"

I need to get all the orders with status CNF without returning PCNF. I could do it using spaces before and after WHERE STATUS LIKE '% CNF %', but if CNF is the first or last status it wouldn't work.

One solution that worked was: 

WHERE 
    PATINDEX('CNF %',STATUS)=0 AND 
    PATINDEX('% CNF %',STATUS)=0 AND
    PATINDEX('% CNF',STATUS)=0

But that is just horrible. Thanks,

Upvotes: 0

Views: 2685

Answers (3)

xlecoustillier
xlecoustillier

Reputation: 16351

As said by Marc B., you should normalize your table to avoid storing more than one value in a single field.

If you don't have the credentials to do that, or if you want to keep your model as it is, you can try to add spaces before and after your string:

WHERE ' '+STATUS+' ' LIKE '% CNF %'

This way you don't have to worry about CNF being first or last item in your list.

I don't know if it's the most elegant/effective solution, but it works.

Upvotes: 2

Nate from Kalamazoo
Nate from Kalamazoo

Reputation: 436

Using SQL 2008's own internal functions, the best I can think of is getting it down to just two conditions like: 

where STATUS like 'CNF%' or STATUS like '%[^P]CNF%'

But if you were willing to install a .Net add-on, you could use regular expressions like so:

where 1 = dbo.RegExpLike(STATUS, '(CNF| CNF)')

Upvotes: 0

Mackie
Mackie

Reputation: 186

Why not simply

WHERE STATUS LIKE '% CNF%' OR STATUS LIKE 'CNF%'

?

The wildcard % matches any character(s), also none.

Upvotes: 0

Related Questions