Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Location
    Fremont, Ohio
    Posts
    3

    Unanswered: Replace Characters

    Hello, perhaps you guys have heard this before in the past, but here is
    what I'm looking for.

    I have a SQL 2005 table with Social security numbers. We need to get rid of the SSN numbers and replace them with SPLAT (*)
    For example:

    if the real SSN is: 340-53-7098 the repacement would be ***-**-****.

    Sounds simply enough, but I can't seem to get it straight.

    I need this to be created using an update query.

    Again, any help would be appreciated it.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Why not just update the table like this:
    Code:
    update table
    set SSN = '***-**-***'

  3. #3
    Join Date
    Sep 2009
    Location
    Fremont, Ohio
    Posts
    3
    Hahaha I wish it was that easy. The Characters that im looking for are located with in a varchar(max) field. People type in paragraphs of data and I'm trying to find socials within those paragraphs

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, you can find them using this:
    Code:
    create table #test
    (col1 varchar(11))
    
    insert into #test values ('111-22-3333')
    insert into #test values ('111-a2-3b33')
    -- End test data
    
    select *
    from #test
    where col1 like '%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%'
    One of the problems you may face is that most of the string functions return varchar(8000), rather than varchar(max). After the 8000th character, you could be losing data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •