We help IT Professionals succeed at work.
Get Started

How to do formatting Phone Numbers in ssis usign expressions usign derived column

Last Modified: 2013-11-10

I need to do phone number formatting in ssis. I get the phone number in the the following format ...
"803-788-7884". The requirement is to be without "-" so it should be finally in this format 8037887884. There might be Nulls or empty spaces and also i need to trim it so that there will not be any truncation for the values while doing substring.

how do i go about it. I did some work on it. I was able to do the following stuff but I am getting error in the derived column itself. It is not accepting the below expression. There might be some parsing error.

Can you throw light on it on where I am going wrong.

ISNULL(Phone_num)||TRIM(Phone_num)==""?NULL(DT_WSTR,5):(DT_STR,10,1252)(SUBSTRING([Phone_num],1,3) + SUBSTRING([Phone_num],5,3) + SUBSTRING([Phone_num],9,4)

Substring part ((DT_STR,10,1252)(SUBSTRING([Phone_num],1,3) + SUBSTRING([Phone_num],5,3) + SUBSTRING([Phone_num],9,4) ) is working fine but if we have NULLS it is giving us NUL as we are doing substring to the NULL but the ISNULL and trimming part is the one that is causing problems.

Also What is wrong in this expression. When I use this follwoing expression REPLACE([Phone_num], "-","")
it is saying truncation error.
Watch Question
Solution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE