SQL Query Tutorial - Searching and Extracting Text

This Post describes how to write SQL query to find out substring from text column and extract substring. I create a dummy table for this tutorial, as you can see:


Now, lets suppose a scenario if  anyone want to extract just city or country name from above table, then how query should be design. There are many SQL functions applied on string. I used string functions for designing this query.

Description of SQL String functions used here:

1- substr(string,position,length): This function takes three arguments i-e String from which user want substring, position means starting point , length means how many characters user want to take in substring. This function returns substring from specified string from starting point(position) to given length argument.

2- instr(string, substring): This function takes two arguments i-e String and substring. It returns position of substring in string.
For more information about SQL functions go here.

I design SQL query for this scenario is:

SELECT
SUBSTR(
loc.locationName,
instr(loc.locationName, 'city') + 5,
instr(loc.locationName, 'country') - instr(loc.locationName, 'city') - 5
) AS 'CITY_NAME'
FROM
location loc;

Above query gives the desired result.

Now, its time to inspect the above query, how it works and how we can get perfect result. This query mainly works on SQL string functions.

SUBSTR(loc.locationName,instr(loc.locationName,'city')+5,instr(loc.locationName,'country')
      -instr(loc.locationName,'city')-5)

In above query part, substr function takes locationName column as String argument of substr function.
 instr(loc.locationName,'city')+5 : This string function returns poisition of substring 'city' in locationName column. Substring city is located at 1 position of locationName column. Like we have string "city London country England". Add 5 in position returned from instr function which place pointer at 'L' character:
"city London country England"

In above locationName column value, pointer is placed just before "London". Now we have to specify number of characters in substr function.

instr(loc.locationName,'country')-instr(loc.locationName,'city')-5

In above query part 1, instr(loc.locationName,'country'): instr function takes locationName and 'country' as function arguments, it returns position of 'country' in locationName string. This function applied to string: "city London country England" returns 13.

In above query part 2, - instr(loc.locationName,'city')-5: instr function takes locationName and 'city' as function arguments, it returns position of 'city' in locationName string. This function applied to string: "city London country England" returns  -1 and do  -5 gives  -6 result.

Now merge both parts gives result like: (13 - 6 = 7) which is the length of substring in locationName column. Its time to place actual values in query to see the working of query.

SELECT
SUBSTR("city London country England",
6 , 7) AS 'CITY_NAME'
FROM
location loc;

Now above query gives London in result.
Hope u enjoy and learn from this tutorial. Give your feedback about this post. :)
Download SQL file for location table from here.