Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
artdavidson
Starting Member
3 Posts |
 Posted - 2010-10-27 : 07:21:05
|
| I have a column of phone number fields. If the phone number's last digit (xxx-xxx-xxxx) has a zero, I need to replace it with a '*'.I've looked into REPLACE & UPDATE statements with no luck. See below example:UPDATE [table name]SET [column name] = RIGHT([column name], 1)& '*'WHERE RIGHT([column name],1) = '0'Any help? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-27 : 07:31:20
|
| UPDATE [table name]SET [column name] = LEFT([column name], DATALENGTH([column name])-1) + '*'WHERE [column name] LIKE '%0'Avoid using functions in WHERE clause if possible as they will be slow. |
 |
|
|
artdavidson
Starting Member
3 Posts |
Posted - 2010-10-27 : 08:34:44
|
quote:
Originally posted by Kristen
UPDATE [table name]SET [column name] = LEFT([column name], DATALENGTH([column name])-1) + '*'WHERE [column name] LIKE '%0'Avoid using functions in WHERE aluse if possible as they will be slow.
Cheers Kristen! What would you recommend instead of the WHERE statement? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-10-27 : 08:46:04
|
quote:
Originally posted by artdavidson
quote:
Originally posted by Kristen
UPDATE [table name]SET [column name] = LEFT([column name], DATALENGTH([column name])-1) + '*'WHERE [column name] LIKE '%0'Avoid using functions in WHERE aluse if possible as they will be slow.
Cheers Kristen! What would you recommend instead of the WHERE statement?
Read his reply again. He did not say to avoid where caluseMadhivananFailing to plan is Planning to fail |
|
|
|
artdavidson
Starting Member
3 Posts |
Posted - 2010-10-27 : 09:05:01
|
quote:
Originally posted by madhivanan
quote:
Originally posted by artdavidson
quote:
Originally posted by Kristen
UPDATE [table name]SET [column name] = LEFT([column name], DATALENGTH([column name])-1) + '*'WHERE [column name] LIKE '%0'Avoid using functions in WHERE aluse if possible as they will be slow.
Cheers Kristen! What would you recommend instead of the WHERE statement?
Read his reply again. He did not say to avoid where caluseMadhivananFailing to plan is Planning to fail
A full explanation would be appreciated. I'm new to SQL - using it at new job in military. |
|
|
|
notmyrealname
98 Posts |
Posted - 2010-10-27 : 11:42:09
|
quote:
Originally posted by artdavidson
If the phone number's last digit (xxx-xxx-xxxx) has a zero, I need to replace it with a '*'.
Do you want to replace the entire phone number or just the last digit (ie '*' or 'xxx-xxx-xxx*') |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-10-27 : 12:37:02
|
quote:
A full explanation would be appreciated. I'm new to SQL - using it at new job in military.
The below is my understanding. Please correct me if i am wrong.Artdavidson - The below explanation is for youKristen said "Avoid using functions in WHERE clause if possible as they will be slow."This was with reference to your Original where statement wherein you have used Right function.quote:
WHERE RIGHT([column name],1) = '0'
Whenever you are using any function on a column in the where clause, if any index is present on the column, it may not be used by SQL optimizer and will defeat the purpose of the index. |
|
|
|
|
|
|
|
|
SQL REPLACE?