Difference between revisions of "Extracting substrings"

Latest revision as of 14: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'

@@ Line 27: / Line 27: @@
 <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)
-  FROM bbc
+  FROM cia
   WHERE region = 'Asia'</source>
 <source lang='sql' class='def e-mysql'>SELECT name, SUBSTRING(name FROM 1 FOR 2)
-  FROM bbc
+  FROM bbc</source>
- WHERE region = 'Asia'</source>
 <source lang='sql' class='def e-mimer'>SELECT name, SUBSTRING(name FROM 1 FOR 2)
   FROM bbc
@@ Line 42: / Line 41: @@
 <div class="ecomm e-oracle" style="display: none">See also SUBSTR
   SELECT name, SUBSTR(name,1,2)
-  FROM cia
+  FROM bbc
   WHERE region = 'Asia'</div>
 </div>
 {{Functions ref}}