Difference between revisions of "Extracting substrings"

From SQLZOO
Jump to: navigation, search
 
(One intermediate revision by one user not shown)
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 cia
+
  FROM bbc
 
  WHERE region = 'Asia'</source>
 
  WHERE region = 'Asia'</source>
<source lang='sql' class='def e-db2'>SELECT name, SUBSTR(name,1,2) FROM cia</source>
+
<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 cia
+
  FROM bbc
 
  WHERE region = 'Asia'</source>
 
  WHERE region = 'Asia'</source>
<source lang='sql' class='def e-access'>SELECT name, MID(name,1,2) FROM cia
+
<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 cia
+
  FROM bbc
 
  WHERE region = 'Asia'</source>
 
  WHERE region = 'Asia'</source>
<source lang='sql' class='def e-oracle'>SELECT name, SUBSTR(name,1,2) FROM cia</source>
+
<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 cia
+
  FROM bbc</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 cia
+
  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 cia
+
  FROM bbc
 
  WHERE region = 'Asia'</source>
 
  WHERE region = 'Asia'</source>
  
Line 42: Line 41:
 
<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 cia
+
  FROM bbc
 
  WHERE region = 'Asia'</div>
 
  WHERE region = 'Asia'</div>
 
</div>
 
</div>
 
{{Functions ref}}
 
{{Functions ref}}

Latest revision as of 15:32, 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
SELECT name, SUBSTRING(name FROM 1 FOR 2)
 FROM bbc
 WHERE region = 'Asia'
SELECT name, SUBSTRING(name ,1 ,2)
 FROM bbc
 WHERE region = 'Asia'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense