Finding a substring in a string
From SQLZoo
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 '% %'