SQL removing spaces, non-numeric and second character from string

Question

I have the following column in my table

MobilePhone
----------
+1 647 555 5556

I want to end up with the following format.

Basically removing the '+' sign, the country code '1' and all spaces.

MobilePhone
----------
6475555556

can someone please point to right direction.

Try to learn about SUBSTRING & REPLACE functions in SQL Server. — Raging Bull
– Raging Bull, Commented Aug 10, 2017 at 17:57

Siyual · Accepted Answer · 2017-08-10 18:20:36Z

3

If they're all US phone numbers, you could try:

select RIGHT(REPLACE(MobilePhone,' ',''),10)
From table

edited Aug 10, 2017 at 18:20

Siyual

17k8 gold badges47 silver badges60 bronze badges

answered Aug 10, 2017 at 17:57

LONG

4,6302 gold badges21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hector Marcia Over a year ago

Thanks LONG, Siyual... that did it!

Alex K. · Accepted Answer · 2017-08-10 18:02:41Z

1

Read past the first space, remove other spaces:

REPLACE(SUBSTRING(MobilePhone, CHARINDEX(' ', MobilePhone, 1), LEN(MobilePhone)), ' ', '')

This assumes your format is strict, if it can be any country code with optional spaces you need another lookup table as they are variable length.

answered Aug 10, 2017 at 18:02

Alex K.

177k32 gold badges276 silver badges299 bronze badges

1 Comment

Koby Douek Over a year ago

That's a better answer than the one I provided. +1 :)

Koby Douek · Accepted Answer · 2017-08-10 18:07:34Z

0

I've nested 2 operations in one UPDATE command. Innermost REPLACE gets rid of the spaces. The RIGHT leaves only 10 characters, that should remove the country code prefix (considering it's US only).

update tbl set MobilePhone= RIGHT(REPLACE(MobilePhone, ' ', ''),10)

edited Aug 10, 2017 at 18:07

answered Aug 10, 2017 at 17:59

Koby Douek

16.7k21 gold badges79 silver badges110 bronze badges

Comments

Soham Bhattacharjee · Accepted Answer · 2017-08-10 18:10:02Z

0

All you need is the right and replace function. Irrespective of the country code: Try running this:

declare @a varchar(30) = '+1 510 999 1234'
declare @b varchar(30) = '+91 98318 12345'

SELECT RIGHT(REPLACE(@a,' ',''),10) --5109991234
SELECT RIGHT(REPLACE(@b,' ',''),10) --9831812345

answered Aug 10, 2017 at 18:10

Soham Bhattacharjee

946 bronze badges

Comments

S3S · Accepted Answer · 2017-08-10 18:32:21Z

0

select replace(REPLACE(MobilePhone,' ',''), '+','')
From table

edited Aug 10, 2017 at 18:32

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered Aug 10, 2017 at 18:04

guru008

1291 silver badge2 bronze badges

Collectives™ on Stack Overflow

SQL removing spaces, non-numeric and second character from string

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related