December 13, 2007 at 5:25 am
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
December 13, 2007 at 5:30 am
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. SelburgDecember 13, 2007 at 5:34 am
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
December 13, 2007 at 5:39 am
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. SelburgDecember 13, 2007 at 6:01 am
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
December 13, 2007 at 6:04 am
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. SelburgDecember 13, 2007 at 6:11 am
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