String Functions

A string function is a function that takes a string value as an input and the return
value (output) can be a string / numeric / character value.
In SQL Server, there are many built-in string functions that can be used by developers
say almost 50.

Sytnax - <string_function_name(argument1[, argument2,argument3])>

Why String Functions - They allow us to alter the individual characters in a string,
compare strings, search strings, to extract substrings, copy of a string. You can also use
these SQL String Functions to convert strings to lowercase or uppercase.

1. UCASE(string) / UPPER(string) - will convert the lower-case alphabets into upper case
of the string in the argument value.

mysql> SELECT UCASE(‘Imagine the wonders’);

+---------------------------------------------------------+
| UCASE(‘Imagine the wonders’) |
+---------------------------------------------------------+
| IMAGINE THE WONDERS |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UCASE(PaymentMode) FROM SALES;

+-----------------------------------------------------------+
| UCASE(PaymentMode) |
+-----------------------------------------------------------+
| CREDIT CARD |
| ONLINE |
| CHEQUE |
| BANK FINANCE |
| CREDIT CARD |
| BANK FINANCE |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> SELECT UPPER(‘MAJOR PRATAP’); ??

mysql> SELECT UPPER(‘20000.00’); ?? 20000.00
mysql>SELECT UPPER(SalePrice)FROM SALES; ??

2. LOWER(string) OR LCASE(string) - will convert the upper-case alphabets into lower

case of the string specified in the argument value.

MySQL> SELECT LCASE('ARMAGEDDON');

+---------------------------------------------------------+
| LCASE(' ARMAGEDDON ') |
+---------------------------------------------------------+
| armageddon |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LIKEWISE

3. SUBSTRING(string, pos, n) – Will return a new string from the main string ‘string’ from
the specified position ‘pos’ and of the length ‘n’.

String – can be any string value.

Pos – will be a numeric value which will specify the starting position of the returned new
string unlike python the starting index address is always 1 never 0 of the first character.

n – will be a numeric value which will specify the total number of characters to be
returned as a new string.

-10 -9 -8 -7 -6 -5 -4 -3 -2 -1

0 1 2 3 4 5 6 7 8 9 10

A R M A G E D D O N

mysql> SELECT SUBSTRING('ARMAGEDDON',3,4);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',3,4)
+------------------------------------------------------------+
| MAGE |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('HAKUNA MATATA',5,5);

+------------------------------------------------------------+
| SUBSTRING('HAKUNA MATATA',5,5)
+------------------------------------------------------------+
| NA MA |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('HAKUNA MATATA',14,2);

+------------------------------------------------------------+
| SUBSTRING('HAKUNA MATATA',14,2)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
0 row in set (0.00 sec)

mysql> SELECT SUBSTRING('HAKUNA MATATA',5,20);

+------------------------------------------------------------+
| SUBSTRING('HAKUNA MATATA',5,20)
+------------------------------------------------------------+
|NA MATATATA |
+------------------------------------------------------------+
0 row in set (0.00 sec)

mysql> SELECT SUBSTRING('HAKUNA MATATA',10);

+------------------------------------------------------------+
| SUBSTRING('HAKUNA MATATA',10)
+------------------------------------------------------------+
| TATA |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON');

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON')
+------------------------------------------------------------+
| Error |
+------------------------------------------------------------+
0 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',0);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',0)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',1);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',1)
+------------------------------------------------------------+
| ARMAGEDDON |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',0,1);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',0,1)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
0 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',1,1);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',1,1)
+------------------------------------------------------------+
| A |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-1);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-1)
+------------------------------------------------------------+
| N |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-3,4);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-3,4)
+------------------------------------------------------------+
| DON |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-8);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-8)
+------------------------------------------------------------+
| MAGEDDON |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-8,4);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-8,4)
+------------------------------------------------------------+
| MAGE |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-8,2);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-8,2)
+------------------------------------------------------------+
| MA |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-4,-2);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-4,-2)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-8,-1);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-8,-1)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-8,0);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-8,0)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
0 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-8,-4);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-8,-4)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',-4,2);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',-4,2)
+------------------------------------------------------------+
| DD |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ARMAGEDDON',4,-2);

+------------------------------------------------------------+
| SUBSTRING('ARMAGEDDON',4,-2)
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING(PaymentMode,8,4) FROM SALES;

+-----------------------------------------------------------+
| SUBSTRING(PaymentMode,8,4) |
+-----------------------------------------------------------+
| Card |
| |
| |
| Nanc |
| Card |
| Nanc |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

*****************************