Post reply

Phone formatting in ssis derived column

  • November 11, 2010 at 12:14 pm

    #228964

    Hi,

    I have a phone field in the excel source. Sometimes it comes as xxx-xxx-xxxx and sometimes xxxxxxxxxx .

    I have to check if the length of the field is 10. If yes then add the dashes else if length is 12 then import as it is.

    Any help appreciated.

    Thanks,

  • November 11, 2010 at 1:02 pm

    #1249688

    PSB (11/11/2010)

    Hi,

    I have a phone field in the excel source. Sometimes it comes as xxx-xxx-xxxx and sometimes xxxxxxxxxx .

    I have to check if the length of the field is 10. If yes then add the dashes else if length is 12 then import as it is.

    Any help appreciated.

    Thanks,

    LEN(PhoneNumber) == 10 ? (DT_STR,12,1252)((SUBSTRING(PhoneNumber,1,3) + "-" + SUBSTRING(PhoneNumber,5,3) + "-" + SUBSTRING(PhoneNumber,9,4)) : PhoneNumber

  • November 11, 2010 at 1:34 pm

    #1249714

    Its complaining about invalid token or may not be well formed.

  • November 11, 2010 at 2:20 pm

    #1249735

    LEN([Phone]) == 10 ? (DT_STR,12,1252)SUBSTRING([Phone],1,3) + "-" + SUBSTRING([Phone],5,3) + "-" + SUBSTRING([Phone],9,4) : LTRIM(RTRIM([Phone]) is inserting as 803-887-84

    instead of 803-788-7884 from the number 8037887884

  • November 11, 2010 at 2:27 pm

    #1249739

    PSB (11/11/2010)

    LEN([Phone]) == 10 ? (DT_STR,12,1252)SUBSTRING([Phone],1,3) + "-" + SUBSTRING([Phone],5,3) + "-" + SUBSTRING([Phone],9,4) : LTRIM(RTRIM([Phone]) is inserting as 803-887-84

    instead of 803-788-7884 from the number 8037887884

    Whoops, I was substringing the value with the dashes instead of the value without the dashes. Try this:

    LEN([Phone]) == 10 ? (DT_STR,12,1252)SUBSTRING([Phone],1,3) + "-" + SUBSTRING([Phone],4,3) + "-" + SUBSTRING([Phone],7,4) : LTRIM(RTRIM([Phone])

  • November 11, 2010 at 2:37 pm

    #1249743

    LEN([Phone]) == 10 ? (DT_STR,12,1252)SUBSTRING([Phone],1,3) + "-" + SUBSTRING([Phone],4,3) + "-" + SUBSTRING([Phone],6,4) : LTRIM(RTRIM([Phone]) worked for me .

    Thanks a lot

  • November 11, 2010 at 2:40 pm

    #1249746

    PSB (11/11/2010)

    LEN([Phone]) == 10 ? (DT_STR,12,1252)SUBSTRING([Phone],1,3) + "-" + SUBSTRING([Phone],4,3) + "-" + SUBSTRING([Phone],6,4) : LTRIM(RTRIM([Phone]) worked for me .

    Thanks a lot

    Any idea why you needed 6,4 in the last SUBSTRING function? It looks like the starting position would be 7 to me...

    Can you explain?

  • November 11, 2010 at 2:51 pm

    #1249750

    Sorry, actually I meant 7.

Viewing 8 posts - 1 through 7 (of 7 total)

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