SQL Substring: How to Use LEFT, RIGHT Functions

In this post I am sharing SQL queries for RIGHT function and LEFT Functions. These two are good to handle strings and useful to extract Substring.

---

SQL String Functions

1. RIGHT Function
2. LEFT Function

SQL RIGHT Function is Scalar Function

My String is “Veera Venkata Vara Prasad”. I need Substring from my full string.

my_full_name is “Veera Venkata Vara Prasad”

SELECT RIGHT(my_full_name,6)
FROM 
SYSIBM.SYSDUMMY1

The result substring is “Prasad”. That means it extracts last 6 chars.

SELECT RIGHT("ANAASALAI",3)
FROM 
SYSIBM.SYSDUMMY1

The result will be “LAI”. The ‘LAI’ is substring.

---

---

SQL LEFT Function is Scalar function

SELECT LEFT(my_full_name,5)
FROM SYSIBM.SYSDUMMY1

The result will be “Veera”

SELECT LEFT("ANAASALAI",3)
FROM SYSIBM.SYSDUMMY1

The result will be “ANA”.

Apart from Trim functions, you can use RIGHT and LEFT functions to handle strings and to get substring.

Top Rules To Use LEFT and RIGHT SQL String Functions

The data type of the result depends on the data type of string-expression:

• VARCHAR if string-expression is CHAR or VARCHAR
• CLOB if string-expression is CLOB
• VARGRAPHIC if string-expression is GRAPHIC or VARGRAPHIC
• DBCLOB if string-expression is DBCLOB
• BLOB if string-expression is BLOB
• The actual length of the result (in string units) is length.
• If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Related Posts