How To Check For Full SubStrings In A Comma Separated String

22/07/2013

How to avoid detecting substrings, how to check for perfect substrings, avoid detecting substrings, avoid detecting substrings when checked for substrings.

Today I needed to find if a string is contained within a string.

So I used the most straight forward method (I used SQL but this works for any language)

declare @msgsToSkip varchar(max) = ‘geoLocation,statusUpdateCheck,forceClose’;
declare @msg varchar(max)= ‘statusUpdate’
if(charindex(@msg, @msgsToSkip) > 0)
begin
print ‘found’
end
else
print ‘not found’

Problem – as you can see, this code is written to messages with caption as either “geoLocation”, “statusUpdateCheck”, or “forcedClose”

But I also receive a message with caption “statusUpdate” this is different to “statusUpdateCheck” and must be allowed but obviously the code returns true for statusUpdate as it is a substring of statusUpdateCheck.

The Solution,

Simply make each word dirty by adding a character to it. It could be any character as long as you keep the code consistent. In more simple words, simply add a character to the string you are

Looking For and the string you are Searching In.

This is the fixed code

declare @msgsToSkip varchar(max) = ‘geoLocation-,statusUpdateCheck-,forceClose-‘; — <– Notice that a ‘-‘ is added to make each word dirty
declare @msg varchar(max)= ‘statusUpdate’
if(charindex(@msg + ‘-‘, @msgsToSkip) > 0) — <- Notice the + ‘-‘
begin
print ‘found’
end
else
print ‘not found’

This will not detect statusUpdate as statusUpdateCheck because at the checking time, the code sees statusUpdate as statusUpdate- and it is not a substring of statusUpdateCheck (note that  statusUpdateCheck is now statusUpdateCheck- but it’s the ‘-‘ in the statusUpdate that does the trick.)

Menol
ILT