Weitere extrahierende String-Funktionen in SQL (MySQL )
SUBSTRING
In der folgenden Beschreibung der Textfunktionen steht vor dem Namen der Funktion der Typ des Aufrufs. In den Klammern stehen kurze Beschreibungen der möglichen Argument, welchen den erwarteten Typ und einen symbolischen Namen umfassen.
»string SUBSTRING( string s, int start, int length )« length Zeichen ab start (das erste Zeichen hat dabei die Kennzahl 1).
- Konsole
SELECT SUBSTRING( 'abc', 0, 0 );
+--------------------------+
| SUBSTRING( 'abc', 0, 0 ) |
+--------------------------+
| |
+--------------------------+SELECT SUBSTRING( 'abc', 0, 1 );
+--------------------------+
| SUBSTRING( 'abc', 0, 1 ) |
+--------------------------+
| |
+--------------------------+SELECT SUBSTRING( 'abc', 1, 1 );
+--------------------------+
| SUBSTRING( 'abc', 1, 1 ) |
+--------------------------+
| a |
+--------------------------+SELECT SUBSTRING( 'abc', 2, 1 );
+--------------------------+
| SUBSTRING( 'abc', 2, 1 ) |
+--------------------------+
| b |
+--------------------------+SELECT SUBSTRING( 'abc', 1, 2 );
+--------------------------+
| SUBSTRING( 'abc', 1, 2 ) |
+--------------------------+
| ab |
+--------------------------+SELECT SUBSTRING( 'abc', 2, 2 );
+--------------------------+
| SUBSTRING( 'abc', 2, 2 ) |
+--------------------------+
| bc |
+--------------------------+SELECT SUBSTRING( 'abc', 1, 0 );
+--------------------------+
| SUBSTRING( 'abc', 1, 0 ) |
+--------------------------+
| |
+--------------------------+SELECT SUBSTRING( 'abc', 1, 3 );
+--------------------------+
| SUBSTRING( 'abc', 1, 3 ) |
+--------------------------+
| abc |
+--------------------------+SELECT SUBSTRING( 'abc', 1, 4 );
+--------------------------+
| SUBSTRING( 'abc', 1, 4 ) |
+--------------------------+
| abc |
+--------------------------+SELECT SUBSTRING( 'abc', 2, 4 );
+--------------------------+
| SUBSTRING( 'abc', 2, 4 ) |
+--------------------------+
| bc |
+--------------------------+SELECT SUBSTRING( 'abc', 2 );
+-----------------------+
| SUBSTRING( 'abc', 2 ) |
+-----------------------+
| bc |
+-----------------------+SELECT SUBSTRING( 'abc', 1 );
+-----------------------+
| SUBSTRING( 'abc', 1 ) |
+-----------------------+
| abc |
+-----------------------+SELECT SUBSTRING( 'abc', 3 );
+-----------------------+
| SUBSTRING( 'abc', 3 ) |
+-----------------------+
| c |
+-----------------------+SELECT * FROM LISTE;
+---------------------+
| LISTE |
+---------------------+
| Alpha |
| Alpha, Gamma |
| Alpha, Gamma, Delta |
+---------------------+SELECT SUBSTRING( LISTE, 6, 1 ) FROM LISTE;
+--------------------------+
| SUBSTRING( LISTE, 6, 1 ) |
+--------------------------+
| |
| , |
| , |
+--------------------------+SELECT SUBSTRING( CONCAT( 'abc', 'def' ), 3, 2 );
+-------------------------------------------+
| SUBSTRING( CONCAT( 'abc', 'def' ), 3, 2 ) |
+-------------------------------------------+
| cd |
+-------------------------------------------+
LEFT
- Konsole
SELECT LEFT( 'abcd', 2 );
+-------------------+
| LEFT( 'abcd', 2 ) |
+-------------------+
| ab |
+-------------------+SELECT LISTE, LEFT( LISTE, 5 ) FROM LISTE;
+---------------------+------------------+
| LISTE | LEFT( LISTE, 5 ) |
+---------------------+------------------+
| Alpha | Alpha |
| Alpha, Gamma | Alpha |
| Alpha, Gamma, Delta | Alpha |
+---------------------+------------------+SELECT LEFT( 1.9999999999999999E0, 1 );
+---------------------------------+
| LEFT( 1.9999999999999999E0, 1 ) |
+---------------------------------+
| 2 |
+---------------------------------+SELECT LEFT( '1.9999999999999999E0', 1 );
+-----------------------------------+
| LEFT( '1.9999999999999999E0', 1 ) |
+-----------------------------------+
| 1 |
+-----------------------------------+
RIGHT
- Konsole
SELECT RIGHT( 'abcd', 2 );
+--------------------+
| RIGHT( 'abcd', 2 ) |
+--------------------+
| cd |
+--------------------+SELECT LISTE, RIGHT( LISTE, 5 ) FROM LISTE;
+---------------------+-------------------+
| LISTE | RIGHT( LISTE, 5 ) |
+---------------------+-------------------+
| Alpha | Alpha |
| Alpha, Gamma | Gamma |
| Alpha, Gamma, Delta | Delta |
+---------------------+-------------------+