strip leading zeroes in sql in string after space

Question

I have a contact number field which stores number as countrycode + ' ' + phonenumber.. Now i want to strip leading zeroes from phone number

I tried using

UPDATE [dbo].[User]
SET PhoneNumber = REPLACE(LTRIM(REPLACE([PhoneNumber], '0', ' ')), ' ', '0')

but this replaces the space in between with '0'

Any suggestions?

If a number is `000232656598`, it should be updated to `232656598` right? — TechDo, Sep 23 '13 at 07:04
This looks like it may already have an answer here: http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server — cbeckner, Sep 23 '13 at 07:09
a number is like "91 009494949494" I need to remove '00' from the phone number "009494949494" — Amey Khadatkar, Sep 23 '13 at 09:41
@AmeyKhadatkar: You should have stated your requirement properly - see my edit. — Sadique, Sep 23 '13 at 12:37

Nisha · Accepted Answer · 2013-09-23T09:52:09.147

1

Try converting the value to int or numeric

Eg:

select '91 004563' as Input, CONVERT(INT, SUBSTRING('91 004563',CHARINDEX(' ','91 004563')+1,100)) as Output

This gives the result

Input       Output
---------   ------
91 004563   4563

edited Sep 23 '13 at 09:52

answered Sep 23 '13 at 07:15

Nisha

1,379
16
28

a number is like "91 009494949494" I need to remove '00' from the phone number "009494949494"..So this won't work – Amey Khadatkar Sep 23 '13 at 09:43
1

try to extract the part after space and convert to `BIGINT`. Then convert back to `VARCHAR` and concatenate. I have edited my post. – Nisha Sep 23 '13 at 09:44

score 1 · Answer 2 · edited Sep 23 '13 at 07:19

1

Try this: SUBSTRING(PhoneNumber, PATINDEX('%[^0 ]%', PhoneNumber + ' '), LEN(PhoneNumber))

edited Sep 23 '13 at 07:19

Milen

8,697
7
43
57

answered Sep 23 '13 at 07:18

Pradeeshnarayan

1,235
10
21

Sadique · Answer 3 · 2013-09-23T12:38:32.860

0

Try:

declare @PhoneNumber varchar(max) = '00000000000000000000001200000031'
while substring(@PhoneNumber,1,1)='0' 
begin
    set  @PhoneNumber = SUBSTRING(@PhoneNumber,2,LEN(@PhoneNumber))
end

select @PhoneNumber

Addressing your comment:

declare @PhoneNumber varchar(max) = '91 00000000000000000000001200000031'
declare @tempphn varchar(max) = substring(@PhoneNumber,4,len(@PhoneNumber)  )
while substring(@tempphn,1,1)='0' 
begin
    set  @tempphn = SUBSTRING(@tempphn,2,LEN(@tempphn))
end

select @tempphn

edited Sep 23 '13 at 12:38

answered Sep 23 '13 at 07:05

Sadique

22,572
7
65
91

Well the solutions posted are already known - i wanted to post something different. User did not mention any performance issues. – Sadique Sep 23 '13 at 09:57

strip leading zeroes in sql in string after space

3 Answers3