SQL Query: How to Use LOCATE and Substring Correctly

Here is an SQL Query to use both LOCATE and SUBSTRING functions. Sometimes the value in the main string is unknown to the programmer, and he has to delimit ‘,’ or ‘space’ from it. For that matter, you need to use both functions.

1. How to Use LOCATE Function

SQL Query to get starting position of search-string using LOCATE function.

SELECT RECEIVED, SUBJECT, LOCATE('GOOD', NOTE_TEXT)
FROM SRINI_LOC_TABLE
WHERE LOCATE('GOOD', NOTE_TEXT) <> 0;

Welcome to

Srinimf

This is my little corner of the Internet, where I talk about solutions to developer’s day to day problems.

New to the site? Start here

2. How to Use both LOCATE and SUBSTRING Functions

First it gets starting position for search string using LOCATE. Later SUBSTRING function gets the string you want.

SELECT RECEIVED, SUBJECT, SUBSTRING(NOTE_TEXT,LOCATE('GOOD', NOTE_TEXT),4)
FROM SRINI_LOC_SUB_TABLE
WHERE LOCATE('GOOD', NOTE_TEXT) <> 0;
Result:
GOOD

Here is another way you can get Substring using LEFT or RIGHT functions, and with specified length.

TIP: In both LOCATE and SUBSTRING the count starts from ‘1’, and backward count starts from -1. This will resolve all Substring problems.

3. Explanation for Locate and SUBSTRING Functions

  1. LOCATE function takes two parameters, one is ‘text you need to find’ and original string column name. You can find this in second query.
  2. Substring function need three parameters as input – Original string column name, start position and length)
  3. In the second query, NOTE_TEXT is column name. LOCATE function gives starting position value. The, the GOOD length is ‘4’. So, the final result from second query is ‘GOOD’.

You may also Like: How to Use LEFT and RIGHT String Functions

3a. Syntax for LOCATE Function

SELECT ABC LOCATE(',', MY_TEXT)
FROM TEST_TABLE;
The result is index of first occurrence of ','.

3b. Syntax for SUBSTRING Function Left to Right

SELECT ABC SUBSTRING(MY_TEXT, 1, 4) 
FROM TEST_TABLE;
The result is substring from position 1 and length 4.

3c. Syntax for SUBSTRING Function Right to Left

SELECT ABC SUBSTRING(MY_TEXT, -2, 3)  
FROM TEST_TABLE;
The result is substring from position -2 and length 3.

Keep Reading

How to Fix Python Syntax Errors

Tips and ideas for developers.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.