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 '% %'
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects