convert Phone Number

Question

Post reply

convert Phone Number

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Check out the max len of that column... Maybe it's over your threshold.

Also check that you're not losing any data between Nchar and char.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

I'm new to SSIS. Where do I check that? The max length of the column on the old legacy side or the new ERP table side? I know that some of the lengths coming from my old system are more than 16, which is the size of the new ERP column. I tried to use ignore failure for truncation and it still errors.

Thanks,

Isabelle

Thanks!
Bea Isabelle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I rarely used DTS and never used SSIS. I'd suggest firing up the management studio and run the queries manually. I usually did those checks like this.

Select * from MYTable where MyCol <> CONVERT(newtype, MyCol)

This is a good place to start, but it may not give you the answer you seek.

Ram Ram Ten Centuries Points: 1079 More actions · Answer 4

You may try this to convert and then do the insert

SET NOCOUNT ON

DECLARE @tbl TABLE (A VARCHAR(30),B VARCHAR(30))

INSERT INTO @tbl(A) SELECT '2015877052'

INSERT INTO @tbl(A) SELECT '800 554-6474'

INSERT INTO @tbl(A) SELECT '(650) 424-9401'

INSERT INTO @tbl(A) SELECT '8004519985 #26207'

INSERT INTO @tbl(A) SELECT '5105355400 x 3433'

INSERT INTO @tbl(A) SELECT '(925) 640-1676-CELL'

SELECT '('+

SUBSTRING(

(CONVERT(VARCHAR(30),LEFT(((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#',''))),

PATINDEX('%[^0-9]%',((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#','')))+' ')-1)))

,1,3)+') '+

SUBSTRING(

(CONVERT(VARCHAR(30),LEFT(((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#',''))),

PATINDEX('%[^0-9]%',((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#','')))+' ')-1)))

,4,3)+'-'+

SUBSTRING(

(CONVERT(VARCHAR(30),LEFT(((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#',''))),

PATINDEX('%[^0-9]%',((REPLACE(REPLACE((REPLACE((REPLACE((REPLACE(A,'(','')),')','')),' ','')),'-',''),'#','')))+' ')-1)))

,7,4)

FROM @tbl

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 5

Wow. I used your suggestion and it did work great. But I'm trying to do this using SSIS and I have an area in the Derived Column area but not sure this will work there. I will give it a try.

Thanks for your suggestion!

Isabelle

Thanks!
Bea Isabelle