Converting 10 digit mobile number in to its standard formats using oracle and sql.


Oracle :

‘(‘||substr(lpad(P.HOME_PHONE,10,’0′),1,3)||’)’||’ ‘||

substr(lpad(P.HOME_PHONE,10,’0′),4,3)||’-‘||

substr(lpad(P.HOME_PHONE,10,’0′),7,4)

WHEN NVL(P.IS_DNCL_MOBILE_PHONE,’N’) =’N’ AND P.MOBILE_PHONE IS NOT NULL THEN

–P.MOBILE_PHONE

‘(‘||substr(lpad(P.MOBILE_PHONE,10,’0′),1,3)||’)’||’ ‘||

substr(lpad(P.MOBILE_PHONE,10,’0′),4,3)||’-‘||

substr(lpad(P.MOBILE_PHONE,10,’0’),7,4)…. more

DESCRIPTION:

ORACLE/PLSQL: SUBSTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.

The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.

SYNTAX

The syntax for the SUBSTR function in Oracle/PLSQL is:

SUBSTR( string, start_position [, length ] )

Parameters or Arguments

string : The source string.

start_position : The starting position for extraction. The first position in the string is always 1.

length : Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

NOTE

  • If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
  • If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
  • If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
  • If length is a negative number, then the SUBSTR function will return a NULL value.
  • See also the REGEXP_SUBSTR function.

APPLIES TO

The SUBSTR function can be used in the following versions of Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let’s look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.

For example:

SUBSTR(‘This is a test’, 6, 2)

Result: ‘is’

Leave a comment

Your email address will not be published. Required fields are marked *