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;

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.

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