Post reply

How do I replace ? in a REPLACE function

  • June 29, 2012 at 4:24 pm

    #260662

    I'd like to run an update statement and replace '?' in some dirty data.

    When I reference ? in a REPLACE function where I want to replace a question mark with NULL I get null for all. i assume its interpreting ? as a wildcard.

    Possible?

    CREATE TABLE #Test (Test char(1))

    INSERT INTO #Test (Test)VALUES ('1')

    INSERT INTO #Test (Test)VALUES ('2')

    INSERT INTO #Test (Test)VALUES ('A')

    INSERT INTO #Test (Test)VALUES ('B')

    INSERT INTO #Test (Test)VALUES ('?')

    SELECT

    Test,

    REPLACE(Test,'?',NULL)

    FROM #Test

    DROP TABLE #Test

  • June 29, 2012 at 4:29 pm

    #1507370

    Replace returns a null if any of the arguments are null, which is why you get nulls for everything in your query. You could do something like this:

    SELECT

    Test,

    nullif('', REPLACE(Test,'?',''))

    FROM #Test

    Edit: This will only work if none of your values are currently a blank. Essentially it replaces the ? with a blank and then puts a null in if it finds a blank.

    Edit2: Maybe I should think before I post. Use this.

    SELECT

    Test,

    Nullif(Test, '?')

    FROM #Test

  • June 29, 2012 at 4:33 pm

    #1507371

    My expected results are:

    1

    2

    A

    B

    NULL

    I want to return any data that is not a question mark.

  • June 29, 2012 at 4:37 pm

    #1507372

    Chrissy321 (6/29/2012)

    My expected results are:

    1

    2

    A

    B

    NULL

    I want to return any data that is not a question mark.

    My most recent edit in the previous post will get you that. Sorry for the confusion.

  • June 29, 2012 at 4:40 pm

    #1507373

    Thank you, appreciate the help.

  • June 30, 2012 at 12:16 am

    #1507424

    select replace(columnname,char(63),'Null')

  • June 30, 2012 at 9:45 pm

    #1507518

    subbareddy542 (6/30/2012)

    select replace(columnname,char(63),'Null')

    I'm thinking that the op wanted a real NULL, not the word "NULL".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 30, 2012 at 10:07 pm

    #1507519

    Whenever you use NULL in an expression, the result is NULL.

    A CASE statement should also work here.

    SELECT Test, CASE WHEN Test = '?' THEN NULL ELSE Test END

    FROM #Test

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • July 7, 2012 at 11:00 pm

    #1510054

    roryp 96873 (6/29/2012)

    Chrissy321 (6/29/2012)

    My expected results are:

    1

    2

    A

    B

    NULL

    I want to return any data that is not a question mark.

    My most recent edit in the previous post will get you that. Sorry for the confusion.

    You can also use like ...

    select * from #Test where replace(Test,char(63),'')<>''

  • July 8, 2012 at 9:26 am

    #1510091

    alokmca1984 (7/7/2012)

    roryp 96873 (6/29/2012)

    Chrissy321 (6/29/2012)

    My expected results are:

    1

    2

    A

    B

    NULL

    I want to return any data that is not a question mark.

    My most recent edit in the previous post will get you that. Sorry for the confusion.

    You can also use like ...

    select * from #Test where replace(Test,char(63),'')<>''

    Careful, now... That will also make it impossible for an INDEX SEEK to occur.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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