CODEWITHSUNDEEP
sqlsql-serverfunction
Hector Marcia
Hector Marcia

Reputation: 103

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

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.

Upvotes: 3

Views: 353

Answers (5)

guru008
guru008

Reputation: 129

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

Upvotes: 0

LONG
LONG

Reputation: 4610

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

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

Upvotes: 3

Soham Bhattacharjee
Soham Bhattacharjee

Reputation: 94

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

Upvotes: 0

Koby Douek
Koby Douek

Reputation: 16675

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)

Upvotes: 0

Alex K.
Alex K.

Reputation: 175748

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.

Upvotes: 1

Related Questions