Need a way to remove the first 1 (if there is one)

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need a way to remove the first 1 (if there is one)

Author

Topic

jarredcody
Starting Member

5 Posts

Posted - 2010-03-24 : 09:20:28

0 vote down
star
Please login or register to use voting.
(click on this box to dismiss)

We have a phone field that allows any entry...

some clients want it in (555) 555-5555 (I know you can do it via a display issue from the software however, the problem is...

I Need a Script to

REMOVE excess characters... leave only numbers and remove the first 1 (if there is one) from the field Ex) "+1 (401) 555-5555 cell"

I want to become "5555555555"

Some items may currently be (555) 555-5555 already.... in that case i want it to become 5555555555

There may need to be two scrips... just not sure of how to go about it

SQL2008

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-24 : 11:16:29

your field can only have numbers and other characters like "+","(",")" ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 11:20:26

try using replace

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 11:21:17

What is the rule to remove (401) but not to remove (555)?

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-24 : 11:21:45

Something like this probably?

declare @t table (phone varchar(20))
insert @t
select '(555) 555-5555' union all
select '+1 (401) 555-5555' union all
select '5555555555'

select right(replace(replace(replace(replace(replace(phone,'(',''),')',''),'+',''),' ',''),'-',''),10) from @t

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-24 : 11:40:22

The difficult part is that per the OP's question there are columns with values like: "+1 (401) 555-5555 cell"

So one would need to remove all non numeric characters and then check to see if the first number is a 1.

What about international numbers? Is that even a concern?

jarredcody
Starting Member

5 Posts

Posted - 2010-03-24 : 11:59:33

What we need is
something to remove all non numeric characters

then run another script to check to see if there is a 1 in the begining of the item... if there is remove the 1.

Sample data (currently)
(401) 623-6559 cell
1-401-921-1899

What we want:
4019211899
4016236559

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 12:09:15

Look here:
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx
and then add this:
select case when left(@result,1)='1' then stuff(@result,1,1,'') else @result end as number

No, you're never too old to Yak'n'Roll if you're too young to die.

jarredcody
Starting Member

5 Posts

Posted - 2010-03-24 : 19:39:10

Help!

I just need a little more help..

the field name is PHONE_NUMBER in the table PHONE
UPDATE [RWD].[dbo].[PHONE]

Declare @s varchar(100),@result varchar(100)set @s='as4khd0939sdf78'

set @result=''
select
@result=@result+case when number like '[0-9]' then number else '' end from
(
select substring(@s,number,1) as number from
(
select number from master..spt_values where type='p' and number between 1 and len(@s)
) as t
) as t
select @result as only_numbers
select case when left(@result,1)='1' then stuff(@result,1,1,'') else @result end as number

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-25 : 04:25:56

You want the 10 digits, counted from right?

N 56°04'39.26"
E 12°55'05.63"

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 05:04:04

Try this


DECLARE @tblph AS TABLE ( Ph varchar(100) )
insert into @tblph 
SELECT PHONE_NUMBER FROM PHONE
--SELECT * FROM @tblph
Declare @s varchar(100), @result varchar(100)

WHILE ( EXISTS( SELECT 1 FROM @tblph ) )
BEGIN
	SELECT TOP 1 @s = ph from @tblph

	set @result=''
	select
	@result=@result+case when number like '[0-9]' then number else '' end from 
	(
	select substring(@s,number,1) as number from master..spt_values where type='p' and number between 1 and len(@s)
	) as t
	select @result = case when left(@result,1)='1' then stuff(@result,1,1,'') else @result end 
	UPDATE PHONE SET PHONE_NUMBER = @Result WHERE PHONE_NUMBER = @s
	DELETE FROM @tblph WHERE ph = @s
END

SELECT * FROM PHONE

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources