Odd Conversion Error

Question

Post reply

Odd Conversion Error

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
June 9, 2013 at 4:20 pm

#273631

Hello Everyone
I would like to remove the '(999)' from the phone number that someone that did not know what they were doing, designed into this table. You should never place the area code and the phone number together. But this is from a DB2 developer. And now I am having to try and deal with it. But when I execute this query, I am getting this error:
Conversion failed when converting the varchar value '(999)4746641' to data type int.
DECLARE @PhoneNumber varchar(15)
DECLARE @NewPhoneNumber varchar(15)
SET @PhoneNumber = '(999)4746641'
SET @NewPhoneNumber = REPLACE(@PhoneNumber,LEFT(5,@PhoneNumber),'')
SELECT @PhoneNumber, @NewPhoneNumber
I would like to remove or replace the area code and the parenthesis from the phone number
Thank you in advance for all your assistance, suggestions and comments
Andrew SQLDBA

Viewing 5 posts - 1 through 4 (of 4 total)

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

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

It looks like you have your arguments mixed up here:

LEFT(5,@PhoneNumber)

Switch them around and see if you get the result you were looking for.

LEFT (Transact-SQL)

Joie Andrew
"Since 1982"

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thanks Joie Andrew

Yes, that was it exactly.

That is just what I deserve for working on a sunday. My mind needs a rest

Thanks

Andrew SQLDBA

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Andrew,

Replace your query with this one:

SET @NewPhoneNumber = REPLACE(@PhoneNumber,LEFT(@PhoneNumber,5),'')

it will works 🙂

You just switch the arguments of LEFT thats why it was not working

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

STUFF() works nicely too:

SELECT

PhoneNumber,

REPLACE(PhoneNumber,LEFT(PhoneNumber,5),''),

STUFF(PhoneNumber,1,5,'')

FROM (

SELECT PhoneNumber = CAST('(999)4746641' AS VARCHAR(15)) UNION ALL

SELECT '(718)4746641'

) d

WHERE LEFT(PhoneNumber,5) = '(999)'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden