Difference between revisions of "Extracting substrings"
From SQLZOO
| Line 14: | Line 14: | ||
<source lang=sql class='setup'></source> | <source lang=sql class='setup'></source> | ||
<source lang='sql' class='def e-sqlite'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | <source lang='sql' class='def e-sqlite'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
| − | <source lang='sql' class='def e-db2'>SELECT name, SUBSTR(name,1,2) FROM | + | <source lang='sql' class='def e-db2'>SELECT name, SUBSTR(name,1,2) FROM bbc</source> |
<source lang='sql' class='def e-ingres'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | <source lang='sql' class='def e-ingres'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
| − | <source lang='sql' class='def e-access'>SELECT name, MID(name,1,2) FROM | + | <source lang='sql' class='def e-access'>SELECT name, MID(name,1,2) FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
<source lang='sql' class='def e-postgres'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | <source lang='sql' class='def e-postgres'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
| − | <source lang='sql' class='def e-oracle'>SELECT name, SUBSTR(name,1,2) FROM | + | <source lang='sql' class='def e-oracle'>SELECT name, SUBSTR(name,1,2) FROM bbc</source> |
<source lang='sql' class='def e-sqlserver'>SELECT name, SUBSTRING(name ,1 ,2) | <source lang='sql' class='def e-sqlserver'>SELECT name, SUBSTRING(name ,1 ,2) | ||
FROM cia | FROM cia | ||
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
<source lang='sql' class='def e-mysql'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | <source lang='sql' class='def e-mysql'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
<source lang='sql' class='def e-mimer'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | <source lang='sql' class='def e-mimer'>SELECT name, SUBSTRING(name FROM 1 FOR 2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
<source lang='sql' class='def e-sybase'>SELECT name, SUBSTRING(name ,1 ,2) | <source lang='sql' class='def e-sybase'>SELECT name, SUBSTRING(name ,1 ,2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</source> | WHERE region = 'Asia'</source> | ||
| Line 42: | Line 42: | ||
<div class="ecomm e-oracle" style="display: none">See also SUBSTR | <div class="ecomm e-oracle" style="display: none">See also SUBSTR | ||
SELECT name, SUBSTR(name,1,2) | SELECT name, SUBSTR(name,1,2) | ||
| − | FROM | + | FROM bbc |
WHERE region = 'Asia'</div> | WHERE region = 'Asia'</div> | ||
</div> | </div> | ||
{{Functions ref}} | {{Functions ref}} | ||
Revision as of 14:29, 17 July 2012
Substring: Extracting part of a string.
schema:gisq
We from position 1 (the beginning) we take two characters. 4.
'Afghanistan' -> 'Af' 'China' -> 'Ch' 'Sri Lanka' -> 'Sr'
The SQL standard insists on a horrible syntax:
SUBSTRING(name FROM 1 FOR 2)
SELECT name, SUBSTRING(name FROM 1 FOR 2) FROM bbc WHERE region = 'Asia'
SELECT name, SUBSTR(name,1,2) FROM bbc
SELECT name, SUBSTRING(name FROM 1 FOR 2) FROM bbc WHERE region = 'Asia'
SELECT name, MID(name,1,2) FROM bbc WHERE region = 'Asia'
SELECT name, SUBSTRING(name FROM 1 FOR 2) FROM bbc WHERE region = 'Asia'
SELECT name, SUBSTR(name,1,2) FROM bbc
SELECT name, SUBSTRING(name ,1 ,2) FROM cia WHERE region = 'Asia'
SELECT name, SUBSTRING(name FROM 1 FOR 2) FROM bbc WHERE region = 'Asia'
SELECT name, SUBSTRING(name FROM 1 FOR 2) FROM bbc WHERE region = 'Asia'
SELECT name, SUBSTRING(name ,1 ,2) FROM bbc WHERE region = 'Asia'
