Teach with us

String Functions in SQL

The string functions in SQL are all used for the manipulation of string values.There are many string functions specified for SQL. Some of these area as follows:

ASCII(str)

This function returns the ASCII or numeric value of the first word in the string str provided. If it is an empty string, it returns 0.

Example

SQL> SELECT ASCII('Sam');
+---------------------------------------------------------+
| ASCII('Sam')                                                 |
+---------------------------------------------------------+
| 83                                                                 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

This returns the ASCII value of S i.e 83 as it is the first character in the string.

BIN(n)

Here n is a binary number and this function provides the string conversion of n. It returns NULL if n is NULL.

Example

SQL> SELECT BIN(10);
+---------------------------------------------------------+
| BIN(10)                                                         |
+---------------------------------------------------------+
| 1010                                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

This provides the string conversion of 10 in binary form i.e 1010.

BIT_LENGTH(str)

This function returns the length of the string str in the form of bits.

SQL> SELECT BIT_LENGTH('ball');
+---------------------------------------------------------+
| BIT_LENGTH('ball')                                      |
+---------------------------------------------------------+
| 32                                                                 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The length of the string “Ball” is 32 bits.

CHAR_LENGTH(str)

This returns the length of str. The length is measured in the form of characters in the string.

SQL> SELECT CHAR_LENGTH("ball");
+---------------------------------------------------------+
| CHAR_LENGTH("ball")                                |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The string “Ball” contains four characters so the output is 4.

CONCAT(str1,str2…..strn)

This function returns the string that forms by concatenating all the strings in the argument list.These strings may be only two or multiple but they will all be concatenated.

Example

SQL> SELECT CONCAT('Apple', 'Is', 'Red');
+---------------------------------------------------------+
| CONCAT('Apple', 'Is', 'Red')                         |
+---------------------------------------------------------+
|AppleIsRed                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Three strings “Apple”,”Is”,”Red” are concatenated into a single string i.e “AppleIsRed”.

CONV(n,base1,base2)

This function converts the number n from base1 to base2. It returns the number n in the form of a string.

SQL> SELECT CONV(‘10',8,2);
+---------------------------------------------------------+
| CONV('10',8,2)                                          |
+---------------------------------------------------------+
| 1000                                                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The value 10 is converted from octal to decimal, which is 1000.

FORMAT(n,d)

This function formats the decimal number n and rounds it to d decimal places.

Example

SQL> SELECT FORMAT(32322.13783, 2);
+---------------------------------------------------------+
| FORMAT(32322.13783, 2)                     |
+---------------------------------------------------------+
| 32,322.14                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The number 32322.13783 is rounded off to 32,322.14 and also formatted i.e appropriate commas are added.

HEX(n)

In this function, the hexadecimal value of n is returned in the form of a string.

Example

SQL> SELECT HEX(175);
+---------------------------------------------------------+
| HEX(175)                                                |
+---------------------------------------------------------+
| AF                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The hexadecimal value of 175 in decimal is AF. That is returned in the form of a string.

LEFT(str,n)

This function returns the leftmost n characters from the string str. If the string is empty, it returns NULL.

Example

SQL> SELECT LEFT('JohnSmith', 4);
+---------------------------------------------------------+
| LEFT('JohnSmith', 4)                                    |
+---------------------------------------------------------+
| John                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

This function returns the leftmost 4 characters from “JohnSmith”. So “John” is returned.

LENGTH(str)

This function returns the length of the string str in bytes.

Example

SQL> SELECT LENGTH('ball');
+---------------------------------------------------------+
| LENGTH('ball')                                          |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The length of the string “ball” is returned in bytes i.e 4.

LOWER(str)

All the characters in uppercase are converted to lowercase by this function.

Example

SQL> SELECT LOWER('CRICKET');
+---------------------------------------------------------+
| LOWER('CRICKET')                                  |
+---------------------------------------------------------+
| cricket                                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

All the characters of “CRICKET” are converted to lowercase i.e “CRICKET”

LPAD(str,l,str pad)

This function rightpads the string str pad to the string str for a length l.If strpad is greater than length l, then the rest of the string is cut.

Example

SQL> SELECT LPAD('Dear',4,'Hi');
+---------------------------------------------------------+
| LPAD('John',4,'Hi')                                     |
+---------------------------------------------------------+
| HiJohn                                                      |
+--------------------------------------------------------+
1 row in set (0.00 sec)

This pads “Hi” to the left of the string “John”, resulting in the string “HiJohn”.

REPLACE(str,s1,s2)

This function replaces all the instances of s1 in string str with s2. Hence, the returned string contains s2 wherever s1 was in string str.

Example

SQL> SELECT REPLACE('Happy', 'p', 'pp');
+---------------------------------------------------------+
| REPLACE('Happy', '', 'pp')                           |
+---------------------------------------------------------+
| Happppy                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

In the string “Happy”, all occurrences of “p” are exchanged with “pp”.

REVERSE(str)

This function reverses the string str and returns it.

Example

SQL> SELECT REVERSE('apple');
+---------------------------------------------------------+
| REVERSE('apple')                                       |
+---------------------------------------------------------+
| elppa                                                            |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The string “apple” is reversed and returned as “elppa”

RIGHT(str,n)

This function returns the rightmost n characters from the string str. If the string is empty, it returns NULL.

Example

SQL> SELECT RIGHT('JohnSmith', 5);
+---------------------------------------------------------+
| RIGHT('JohnSmith', 5)                                  |
+---------------------------------------------------------+
| Smith                                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

This function returns the rightmost five characters i.e.  “Smith” from the string “JohnSmith”

RPAD(str,l,str pad)

This function rightpads the string str pad to the string str for a length l.If strpad is greater than length l, then the rest of the string is cut.

SQL> SELECT RPAD('hello',5,'!');
+---------------------------------------------------------+
| RPAD('hello',5,'!')                                        |
+---------------------------------------------------------+
| hello!                                                           |
+---------------------------------------------------------+
1 row in set (0.00 sec)

This function pads the string”!” to the right side of the string “hello”.

STRCMP(str1,str2)

This function compares both the strings str1 and str2. It returns 0 if both strings are equal, 1 if str1 is greater than str2 and -1 if if str2 is greater than str1.

SQL> SELECT STRCMP('HARRY', 'HARRY');
+---------------------------------------------------------+
| STRCMP('HARRY', 'HARRY')                                |
+---------------------------------------------------------+
| 0                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The function returns 0 as the strings “HARRY” and “HARRY” are identical.

UPPER(str)

All the characters in lowercase are converted to uppercase by this function.

SQL> SELECT UPPER('apple');
+---------------------------------------------------------+
| UPPER('apple')                                          |
+---------------------------------------------------------+
| APPLE|
+---------------------------------------------------------+
1 row in set (0.00 sec)

The string “car” is converted to ”CAR” in uppercase.

Updated on 19-Jun-2020 14:35:42

0 Views

Tutorials Point

Previous Page Next Page  
Advertisements