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
- RIGHT Function
- 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