+ Reply to Thread
Results 1 to 7 of 7

Converting Numbers to a SSN Format

  1. Converting Numbers to a SSN Format

    I inherited a db that allowed for multiple types of social security numbers
    to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
    (this latter being a pseudo based on initials of the client and birth date),
    which could also be ABC021987.

    I need to update the fields in the system so they all have the format of
    ###-##-#### (with the # being alpha-numeric)

    I know the update syntax and how to manipulate to upper or lower case, but
    cannot find an example like what I need.

    Any suggestions?


  2. Re: Converting Numbers to a SSN Format

    Why do you need to actually update the data? This means you need to update
    it whenever you touch it, because the very next update or insert could
    introduce new unformatted values. I would prefer a computed column or a
    view.

    Anyway, SQL Server does not support input mask. You can use regular string
    parsing / concatenation or STUFF(). The latter is less code but tougher to
    perfect/debug so I will show the former.

    SELECT LEFT(col, 3) + '-' + SUBSTRING(col, 4, 2) + '-' + RIGHT(col, 4)
    FROM (SELECT col = '555667777') x;





    "JOHN HARRIS" wrote in message
    news:99B8C96B-AE61-4612-BAA4-C471FE3A3DB9@microsoft.com...
    >I inherited a db that allowed for multiple types of social security numbers
    >to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
    >(this latter being a pseudo based on initials of the client and birth
    >date), which could also be ABC021987.
    >
    > I need to update the fields in the system so they all have the format of
    > ###-##-#### (with the # being alpha-numeric)
    >
    > I know the update syntax and how to manipulate to upper or lower case, but
    > cannot find an example like what I need.
    >
    > Any suggestions?




  3. Re: Converting Numbers to a SSN Format

    Assuming the only extra character in the existing data is -, first
    remove all the - characters and then put them where you want them.

    CREATE TABLE #Demo (SSN varchar(15))

    INSERT #Demo VALUES('555667777')
    INSERT #Demo VALUES('555-66-7777')
    INSERT #Demo VALUES('ABC-02-1987')

    SELECT SUBSTRING(REPLACE(SSN,'-',''),1,3) + '-' +
    SUBSTRING(REPLACE(SSN,'-',''),4,2) + '-' +
    SUBSTRING(REPLACE(SSN,'-',''),6,4)
    FROM #Demo

    I assume you will know how to change this into an UPDATE if required.

    Roy Harvey
    Beacon Falls, CT

    On Tue, 28 Aug 2007 14:39:04 -0400, "JOHN HARRIS"
    wrote:

    >I inherited a db that allowed for multiple types of social security numbers
    >to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
    >(this latter being a pseudo based on initials of the client and birth date),
    >which could also be ABC021987.
    >
    >I need to update the fields in the system so they all have the format of
    >###-##-#### (with the # being alpha-numeric)
    >
    >I know the update syntax and how to manipulate to upper or lower case, but
    >cannot find an example like what I need.
    >
    >Any suggestions?


  4. Re: Converting Numbers to a SSN Format

    SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
    as SSN, Clients.First_Name + ' ' + Clients.Last_Name
    FROM Clients

    It recodes the SSN as I need it. How do I get this result to the UPDATE
    function inside the main db?

    Possibly:

    Update Clients
    Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)


    "Aaron Bertrand [SQL Server MVP]" wrote in message
    news:OcOp1La6HHA.484@TK2MSFTNGP06.phx.gbl...
    > Why do you need to actually update the data? This means you need to
    > update it whenever you touch it, because the very next update or insert
    > could introduce new unformatted values. I would prefer a computed column
    > or a view.
    >
    > Anyway, SQL Server does not support input mask. You can use regular
    > string parsing / concatenation or STUFF(). The latter is less code but
    > tougher to perfect/debug so I will show the former.
    >
    > SELECT LEFT(col, 3) + '-' + SUBSTRING(col, 4, 2) + '-' + RIGHT(col, 4)
    > FROM (SELECT col = '555667777') x;
    >
    >
    >
    >
    >
    > "JOHN HARRIS" wrote in message
    > news:99B8C96B-AE61-4612-BAA4-C471FE3A3DB9@microsoft.com...
    >>I inherited a db that allowed for multiple types of social security
    >>numbers to be entered, from examples like 555667777 to 555-66-7777 to
    >>ABC-02-1987 (this latter being a pseudo based on initials of the client
    >>and birth date), which could also be ABC021987.
    >>
    >> I need to update the fields in the system so they all have the format of
    >> ###-##-#### (with the # being alpha-numeric)
    >>
    >> I know the update syntax and how to manipulate to upper or lower case,
    >> but cannot find an example like what I need.
    >>
    >> Any suggestions?

    >
    >



  5. Re: Converting Numbers to a SSN Format

    > It recodes the SSN as I need it. How do I get this result to the UPDATE
    > function inside the main db?
    >
    > Possibly:
    >
    > Update Clients
    > Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
    > 4)



    Take a look at the UPDATE topic in Books Online. You are saying SET but you
    are not telling SQL Server which column you are talking about.


    Update Clients
    Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
    RIGHT(ssnum, 4)


    You probably want a where clause on there, just in case you already have
    rows that are in the desired format. This statement will affect all rows,
    and if any contain dashes, now they will contain more.

    A



  6. Re: Converting Numbers to a SSN Format

    Yeah the WHERE Clause almost bit me. What type of where clause should I use
    to not harm those entered correctly?

    Something like:

    Update Clients
    Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
    RIGHT(ssnum, 4)

    WHERE ssnum not like '___-__-____'

    ??

    "Aaron Bertrand [SQL Server MVP]" wrote in message
    news:uIrLUr5BIHA.912@TK2MSFTNGP05.phx.gbl...
    >> It recodes the SSN as I need it. How do I get this result to the UPDATE
    >> function inside the main db?
    >>
    >> Possibly:
    >>
    >> Update Clients
    >> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
    >> 4)

    >
    >
    > Take a look at the UPDATE topic in Books Online. You are saying SET but
    > you are not telling SQL Server which column you are talking about.
    >
    >
    > Update Clients
    > Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
    > RIGHT(ssnum, 4)
    >
    >
    > You probably want a where clause on there, just in case you already have
    > rows that are in the desired format. This statement will affect all rows,
    > and if any contain dashes, now they will contain more.
    >
    > A
    >



  7. Re: Converting Numbers to a SSN Format

    Well, you can always run a SELECT (not an UPDATE) and check and make sure
    that the LIKE and NOT LIKE versions of that query would affect the row(s)
    you expect.




    "JOHN HARRIS" wrote in message
    news:82851662-342B-4866-8130-B2AC00ACB84C@microsoft.com...
    > Yeah the WHERE Clause almost bit me. What type of where clause should I
    > use to not harm those entered correctly?
    >
    > Something like:
    >
    > Update Clients
    > Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
    > RIGHT(ssnum, 4)
    >
    > WHERE ssnum not like '___-__-____'
    >
    > ??




+ Reply to Thread