Finding a substring in a string

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 '% %'

CHARINDEX gives the position, see also PATINDEX

Functions

Concatenate strings

Extracting substrings

lower case

Finding Substring

Formatting numbers

Replace NULL

Conditional values

Time now

Format dates