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.

Answer 1

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

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

Answer 2

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.