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.