Finding a substring in a string

From SQLZOO
Revision as of 15:42, 17 July 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, 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 '% %'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense