Trimming a digit if it exists

Question

Trimming a digit if it exists

Glenn5709

Grasshopper

Points: 13
More actions
May 10, 2010 at 2:14 am

#74289

Hi all!
I am new to all this SQL malarkey, but i am learning it fairly quickly. I have managed to pick up a bit by working through things that i need to do and am quite happy with the bits that i know. However, there is one thing that i am completley stuck on and all of my attempts have failed thus far.
We have a list of telephone numbers, some which are prefixed with the number 9 (external trunk access code). When generating reports, i would like to trim the 9 if it exists as not all of the phone numbers have the prefix. The query that i used when running the report via Access was:
tel_no: IIf(Left([cont_tel_no],1)="9",Right([cont_tel_no],Len([cont_tel_no])-1),[cont_tel_no])
I have had many failed attempts of using SQL to execute the same query, but failed 🙁
If anybody could shed some light on this i would be eternally grateful.

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

IIF is an access function that doesn’t exist in SQL Server. You can use the CASE function to check if the phone number begins with 9 and then decide if you show the full number of only the part of the number from the second digit. Here is a small demo that show how to do it:

declare @PhoneNum varchar(15)

set @PhoneNum = '90529702610'

select case when LEFT(@PhoneNum,1) = '9' then RIGHT(@PhoneNum,LEN(@PhoneNum)-1) else @PhoneNum end

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Glenn5709 Grasshopper Points: 13 More actions · Answer 2

Thank you very much for your input. I have used what you mentioned and created a view

CREATE VIEW [000 - ContactNumber]

AS

SELECT

client_no,

tel_no1,

CASE

WHEN LEFT(tel_no1,1) = '9' THEN RIGHT(tel_no1,LEN(tel_no1)-1)

ELSE tel_no1

END AS 'ContactNumber1'

FROM dbo.hgmclent

GO

I was aware that an Access 'IIF' statement would not work, which is where i was failing as i didn't know how it could be done in SQL talk.

Thanks! 😀

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Be careful with this approach.

It would fail for strings with spaces on the right.

Try the same example with '90529702610 ' and see it for yourself.

Use this instead:

SUBSTRING (Phone, 2, LEN(Phone))

P.S. Are you sure no phone numbers in your area are started with "9"?

_____________
Code for TallyGenerator

Glenn5709 Grasshopper Points: 13 More actions · Answer 4

Our database consists of phone numbers from all over the UK, so all of our phone numbers are inclusive of an area code therefore starting with 01, 02 or 03.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Glenn5709 (5/10/2010)
Our database consists of phone numbers from all over the UK, so all of our phone numbers are inclusive of an area code therefore starting with 01, 02 or 03.

Understood... but that wasn't Sergiy's point. Try the following code and see...

SELECT '90529702610 ' AS Original,

CASE

WHEN LEFT('90529702610 ',1) = '9' THEN RIGHT('90529702610 ',LEN('90529702610 ')-1)

ELSE '90529702610 '

END AS WrongWay,

SUBSTRING ('90529702610 ', 2, LEN('90529702610 ')) AS RightWay

--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)

Sergiy SSC Guru Points: 110209 More actions · Answer 6

Then you need to make sure users ALWAYS enter area codes, and prefix it with zeros.

Otherwise some local numbers like "952-1154" will be truncated without warning.

Apart from another failure point nicely illustrated by Jeff's example.

_____________
Code for TallyGenerator