String Functions in SQL

Read this Oracle Tutorial Point article to know SUBSTR, LENGTH and INSTR functions in String functions in SQL in Oracle database.

SUBSTR(SUBSTRING) Function:

SUBSTR function returns described characters from character value, starting from a specified position ‘m’, to ‘n’ character long.

Important Points:

• If m is 0, it is treated as 1.
• If m is Positive(+), Oracle counts from the beginning of char to identify the first character.
• If m is Negative(-), Oracle counts backwards from the end of the character.
• If n is Omitted, Oracle returns all characters to last of Char.
• If n is less than 1 or 0, a NULL is returned.

Floating point numbers passed as arguments to SUBSTR are automatically converted to Integers.

Syntax:

• SUBSTR(Col/Expr,m,n)

Examples:

SQL> SELECT SUBSTR('ABCDEFGHIJK',3,4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',-5,4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',0,4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',4) FROM DUAL;

SQL> SELECT SUBSTR('ABCDEFGHIJK',4,-2) FROM DUAL;

LENGTH Function:
Return the number of characters in a value. If the char has data type CHAR, the length includes all trailing blanks. If the char is NULL, it returns NULL.

Syntax:

• LENGTH(Column/Expression)

Examples:

SQL> SELECT LENGTH ('SUBBAREDDY') FROM DUAL;

SQL> SELECT LENGTH(NAME) || ' Characters exit in ' || INITCAP(NAME) || 

's Name.' AS ' Names and Lengths ' FROM EMP;

SQL> SELECT INITCAP(NAME), JOB FROM EMP WHERE LENGTH(JOB)=7;

INSTR(INSTRING) Function:
It returns the numeric position of a named character.

Syntax:

• INSTR (Column/Expression, Char, n,m)

Important Points:

• Searches for Column / Expression beginning with its n’th character for the m’th occurrence of char2 and returns the position of the character of this occurrence.
• N can be positive or negative, if negative searches backward from the end of column expression.
• The value of ‘m’ should be positive.
• The default values of both m and n are 1.
• The return value is relative to the beginning of char1 regardless of the values of n and is expressed in characters.
• If the search is unsuccessful, the return value is zero.

Examples:

SQL> SELECT INSTR('String','r') FROM DUAL;

SQL> SELECT INSTR('CORPORATE LOOR','OR',3,2) FROM DUAL;

SQL> SELECT INSTR('CORPORATE FLOOR','OR',-3,2) FROM DUAL;

SQL> SELECT JOB,INSTR(JOB,'A',1,2) FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,INSTR(JOB,'A',2,2) FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,INSTR(JOB,'A',2) FROM EMP WHERE JOB='MANAGER';