How to Use LEFT, RIGHT Substring Functions

Here are best examples on how to use SQL RIGHT or LEFT Function. Using these you can work with strings or Substrings.

SQL String Functions

1. RIGHT Function
2. LEFT Function

SQL RIGHT Function

RIGHT function is scalar. 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

LEFT function is Scalar.

How to use LEFT 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.

Rules for LEFT and RIGHT 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