Searching strings in SQL with LOCATE_IN_STRING

I’ve been somewhat remiss in maintaining this blog over the past few months, but the LOCATE_IN_STRING scalar function really is worth mentioning.

The LOCATE_IN_STRING function returns the starting position of a string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string. If the optional start is specified, it indicates the character position in the source-string at which the search is to begin.

The format of the function is LOCATE_IN_STRING(source_string, search_string, start, instance)

If the optional start is specified, it indicates the character position in the source-string at which the search is to begin. If start is specified, an optional instance number can also be specified. The instance argument is used to determine the specific occurrence of search-string within source-string. Each unique instance can include any of the characters in a previous instance, but not all characters in a previous instance.

What this means is that not only can you find strings containing a search text, but you can also determine exactly where in the source string that text is.

INSTR can be used as a synonym for LOCATE_IN_STRING. I have used this shorter form in the following example.

select 
    INSTR('TEST TEXT IS A TEST', 'TEST') a, 
    INSTR('TEST TEXT IS A TEST', 'TEST', 1, 2) b,
    INSTR('TEST TEXT IS A TEST', 'TEST', -1) c,
    INSTR('TEST TEXT IS A TEST', 'TEST', -1, 2) d,
    INSTR('TEST TEXT IS A TEST', 'TEST', 5) e, 
    INSTR('TEST TEXT IS A TEST', 'TEST', -5) f 
from SYSIBM.SYSDUMMY1

Returns:

A               B               C               D               E               F 
1              16              16               1              16               1

Column A looks for the first instance of ‘TEST’ in the source string and finds it in position 1. Column B is looking for the second instance of the same string and finds it in position 16. All pretty straightforward so far.

With column C, things get a bit more interesting. Starting at position -1 tells the function to search from right to left, starting at the rightmost position in the source string. And in column D, I’m looking for the second instance of the search string, starting from the right.

And finally, in columns E and F, I am starting the search five positions in.

LOCATE_IN_STRING was introduced in 7.1 and provides an effective method of searching within strings without having to resort to regular expressions.

Leave a Reply Cancel reply