Remove the character "-" in a field and close up field afterwards

  • I'm trying to figure out how to remove a character that might appear anywhere in a field and then bring together the part of the field that appears before the character and the part that appears after.

    Example: smith-jones

    I want it to end up being smithjones

    Example: mccarthy-kennedy

    I want it to end up being mccarthykennedy

    Because the "-" charcter can appear in different positions within the field, I don't know how to write one expression that will cover all possibilities.

    Any help would be appreciated.

    Roger Abram

    rabram@naifa.org

  • Use replace ...

    SELECT REPLACE('smith-jones-lewis', '-','')

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Thank you... I should have mentioned I need to go through a 300,000+ name database to do this and don't know all of the occurences or would want to type them all in manually.

    So I need something to run on a global scale and not a case by case basis.

    Thank you.

    Roger Abram

    rabram@naifa.org

  • Then ....

    UPDATE yourTableNameHere

    SET yourFieldNameHere = REPLACE(yourFieldNameHere, '-','')

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Excellent! That worked just fine.

    One more question, I also need to do it for anyone that has an apostrophe in their last name but I'm running into issues with SQL because of the apostrophes already in the update script.

    If I run this (to change a name like O'Brien)

    UPDATE tablename

    SET fieldname = REPLACE(fieldname, ''','')

    I get the following:

    Server: Msg 105, Level 15, State 1, Line 2

    Unclosed quotation mark before the character string '',')

    '.

    Server: Msg 170, Level 15, State 1, Line 2

    Line 2: Incorrect syntax near '',')

    '.

    Thoughts?

    Thanks again.

    Roger

  • Use four apostrophes.

    UPDATE yourTableNameHere

    SET yourFieldNameHere = REPLACE(yourFieldNameHere, '''','')

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Excellent! Thank you for all of your help!

    Roger

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

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