Check if string doesn't contain another string

Question

In T-SQL, how would you check if a string doesn't contain another string?

I have an nvarchar which could be "Oranges Apples".

I would like to do an update where, for instance, a columm doesn't contain "Apples".

How can this be done?

Solution 1

WHERE NOT (someColumn LIKE '%Apples%')

Solution 2

Or alternatively, you could use this:

WHERE CHARINDEX(N'Apples', someColumn) = 0

Not sure which one performs better - you gotta test it! :-)

Marc

UPDATE: the performance seems to be pretty much on a par with the other solution (WHERE someColumn NOT LIKE '%Apples%') - so it's really just a question of your personal preference.

Solution 3

Use this as your WHERE condition

WHERE CHARINDEX('Apples', column) = 0 

Solution 4

The answers you got assumed static text to compare against. If you want to compare against another column (say, you're joining two tables, and want to find ones where a column from one table is part of a column from another table), you can do this

WHERE NOT (someColumn LIKE '%' || someOtherColumn || '%')

View more solutions

Comments

[Dofs]

Why didn't I think of that, I was going for something much more advanced :)

[DeveloperDan]

Use single quotes instead of double: '%Apples%'. I tried to edit the post but SO requires edits to be six characters or more.

[Joe]

Won't work if someOtherColumn contains a wildcard character such as '%', '_'.

[Joe]

+1: If the text to be matched is dynamic rather than a static string such as 'Apples', then this method is superior, because you don't need to worry about wildcards such as '%', '_' that will be matched by the LIKE operator.

[Joe]

-1: OK for the static string 'Apples', but won't work for a dynamic string that might contain a wildcard such as '%', '_'. For example, if you search for columns that contain '5% reduction', the LIKE operator will also match those that contain '50% reduction' and '5 cents reduction'. The solution from @marc_s using CHARINDEX will work as expected in these cases and IMHO should be the accepted answer.

[DDuffy]

@Joe If you want to be pedantic about it, then this one wouldn't work in all cases either. CHARINDEX is not case sensitive. so if you were looking for an Apple in a field of apples you would get the whole lot. I know its been a few years since this was posted, but still. Both answers are valid depending on the subject matter and no one answer covers all bases. a -1 from you for the accepted answer just seems a bit... is pernickety the right word?

[Joe]

@DDuffy - both LIKE and CHARINDEX are affected by the input collation, which may or may not be case-sensitive. For matching a string input by the user, LIKE is appropriate if the user is expected to know how to use and escape wildcards like %; CHARINDEX is better if the user is just wanting to match text (e.g. to match all values that contain '2% discount', without inadvertently including those that have 20% discount).

[user3088037]

I would wrap someColumn as ISNULL(someColumn,'') for if someColumn contains NULL the expression above won't work as expected