How to convert 10 digit mobile number in to its format of (000)000-0000 using oracle and sql..

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

Purpose:

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:

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'

Technways