Finding a substring in a string

From SQLZOO
Jump to navigation Jump to search

Finding a substring in a string

schema:gisq

Here we extract the first word of a country name. INSTR gives this position of one string within another, we use this and substring to pick out the first few characters. 

SELECT name,
       POSITION(' ' IN name),
       SUBSTRING(name FROM 1 FOR POSITION(' ' IN name))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       POSITION(' ' IN name),
       SUBSTRING(name FROM 1 FOR POSITION(' ' IN name))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       POSITION(' ' IN name),
       SUBSTRING(name FROM 1 FOR POSITION(' ' IN name))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       INSTR(name, ' '),
       SUBSTRING(name,1,INSTR(name,' '))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       POSITION(' ' IN name),
       SUBSTRING(name FROM 1 FOR POSITION(' ' IN name))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       INSTR(name, ' '),
       SUBSTR(name,1,INSTR(name,' '))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
     CHARINDEX(' ',name),
     SUBSTRING(name,1,CHARINDEX(' ',name)-1)
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       POSITION(' ' IN name),
       SUBSTRING(name FROM 1 FOR POSITION(' ' IN name))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
       POSITION(' ' IN name),
       SUBSTRING(name FROM 1 FOR POSITION(' ' IN name))
  FROM bbc
  WHERE name LIKE '% %'

SELECT name,
     CHARINDEX(' ',name),
     SUBSTRING(name,1,CHARINDEX(' ',name)-1)
  FROM bbc
  WHERE name LIKE '% %'
Functions
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects
Retrieved from "https://sqlzoo.net/w/index.php?title=Finding_a_substring_in_a_string&oldid=1530"