Simple Searching for Substrings

Two functions are useful when you need to find simple substrings within other strings   The two functions are CHARINDEX and STRPOS are identical except for the order of the parameters you pass to them.  Both functions return the position of the found string within the initial string, or return 0 if it was not found.

Example: Find the string 'DE' within the string 'ABCDEF'


Option 2: STRPOS(‘ABCDEF’, ‘DE’)

Both return the value 4 since 'DE' is found starting at the 4th position of the string 'ABCDEF'.

Note of course that typically the initial string is a variable in your dataset, so the 'ABCDEF' part of this example would be replaced that a reference to that variable, as in:

CHARINDEX(‘DE’, [mystring]) or STRPOS([mystring], 'DE')


For more complex pattern matching, see the functions here: