Written by Sai Sandeep Thota on February 6, 2013 in Programming > SQL

Basic SQL String Functions

GET ALERTS:

Get our Latest updates delivered to your mailbox!

In the earlier article about SQL, we have discussed about some basic SQL queries. In this article we will be discussing about basic SQL string functions. Don’t forget to bookmark this SQL String Functions list article, as they are very useful.

Basic SQL String Functions

Know ASCII Value of a Word:

mysql> SELECT ASCII('A') FROM dual;
Output:
+------------+
| ASCII('A') |
+------------+
|         65 |
+------------+
1 row in set (0.00 sec)

ASCII() function is same in both Oracle and MySQL.

Return Word from ASCII Value:

The below function works with Oracle, But you need to use CHAR() in the case of MySQL.
Oracle:

sql> SELECT CHR(65) FROM dual;
+----------+
| CHR(65)  |
+----------+
| A        |
+----------+

MySQL:

mysql> SELECT CHAR(65) FROM dual;
+----------+
| CHAR(65) |
+----------+
| A        |
+----------+
1 row in set (0.00 sec)

String Concatenation:

mysql> SELECT CONCAT('San','deep') FROM dual;
+----------------------+
| CONCAT('San','deep') |
+----------------------+
| Sandeep              |
+----------------------+
1 row in set (0.07 sec)

CONCAT() function is same in both Oracle and MySQL.

Find Length of a String:

mysql> SELECT LENGTH('Sandeep') FROM dual;
+-------------------+
| LENGTH('SANDEEP') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.06 sec)

LENGTH() function is same in both Oracle and MySQL.

Replace a String:

mysql> SELECT REPLACE('Sandeep','San','Saan') FROM dual;
+---------------------------------+
| REPLACE('Sandeep','San','Saan') |
+---------------------------------+
| Saandeep                        |
+---------------------------------+
1 row in set (0.00 sec)

The basic Function structure looks like this:

mysql> SELECT REPLACE('Name','OLDSTRING','NEWSTRING') FROM dual;

REPLACE() function is same in both Oracle and MySQL.

Get Substring from a String:

mysql> SELECT SUBSTR('RAVI',2,3) FROM dual;
+--------------------+
| SUBSTR('RAVI',2,3) |
+--------------------+
| AVI                |
+--------------------+
1 row in set (0.00 sec)

SUBSTR() function is same in both Oracle and MySQL.

Left-Padded String:

This function is used to return a left-padded string argument with specified string.

mysql>SELECT LPAD('SANDEEP',10,'*') FROM dual;
+------------------------+
| LPAD('SANDEEP',10,'*') |
+------------------------+
| ***SANDEEP             |
+------------------------+
1 row in set (0.00 sec)

Basic function structure:

mysql> SELECT LPAD('Original String',integer,'replaced_string') FROM dual;

LPAD() function is same in both Oracle and MySQL.

Right-Padded String:

This function is used to return a right-padded string argument with specified string.

mysql> SELECT RPAD('SANDEEP',10,'?') FROM dual;
+------------------------+
| RPAD('SANDEEP',10,'?') |
+------------------------+
| SANDEEP???             |
+------------------------+
1 row in set (0.00 sec)

RPAD() function is same in both Oracle and MySQL.

Remove Trailing Spaces:

To remove trailing spaces on the left side of a string:

mysql> SELECT LTRIM('  SANDEEP') FROM dual;
+--------------------+
| LTRIM('  SANDEEP') |
+--------------------+
| SANDEEP            |
+--------------------+
1 row in set (0.00 sec)

To remove trailing spaces on the right side of a string:

mysql> SELECT RTRIM('SANDEEP  ') FROM dual;
+--------------------+
| RTRIM('SANDEEP  ') |
+--------------------+
| SANDEEP            |
+--------------------+
1 row in set (0.00 sec)

To remove trailing spaces from either directors:

mysql> SELECT TRIM('  SANDEEP  ') FROM dual;
+---------------------+
| TRIM('  SANDEEP  ') |
+---------------------+
| SANDEEP             |
+---------------------+
1 row in set (0.00 sec)

LTRIM(), RTRIM(), TRIM() function are same in both Oracle and MySQL.

Hope you have understood the basic SQL String Functions. You can subscribe to our Mailing list to receive some awesome stuff!

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: