Replace Spaces in Telephone Column

Question

I currently have a telephone number column which has spaces in between the numbers.

For example:

07595 8832 36
0161 88143 09
016188121 1 1
0 7 585 99 21 2 2

How do I replaces the spaces and get the number all together?

Example:

07595 8832 36

07595883236 (like this)

The table name is called [dbo].[NumberChecker3]

And the column in the database is called Telephone

I think this might help you http://stackoverflow.com/questions/9621778/how-to-replace-a-character-from-a-string-in-sql — VladH, Feb 25 '14 at 11:17
possible duplicate of [How to replace a string in a SQL Server Table Column](http://stackoverflow.com/questions/814548/how-to-replace-a-string-in-a-sql-server-table-column) — t-clausen.dk, Feb 25 '14 at 12:45

score 1 · Answer 1 · answered Feb 25 '14 at 11:16

1

REPLACE (phone_number, ' ', '')

answered Feb 25 '14 at 11:16

GriGrim

score 1 · Answer 2 · answered Feb 25 '14 at 11:20

1

Like this:

UPDATE [dbo].[NumberChecker3] SET telephone = REPLACE (telephone , ' ', '')

answered Feb 25 '14 at 11:20

jpw

score 1 · Answer 3 · answered Feb 25 '14 at 15:05

try:

select replace(replace( rtrim(replace(Telephone,char(160),'')) , char(9),''), ' ','')
from [dbo].[NumberChecker3]

if work fine for you then:

update [dbo].[NumberChecker3]
set Telephone = replace(replace( rtrim(replace(Telephone,char(160),'')) , char(9),''), ' ','')

score 0 · Answer 4 · answered Feb 25 '14 at 11:24

try this

UPDATE [dbo].[NumberChecker3] SET [Telephone] = REPLACE (Telephone , ' ', '')

this is for the update or if you just need to get the number without space then try this

Declare @number nvarchar(10);
select @number = REPLACE (Telephone , ' ', '') From [dbo].[NumberChecker3]

score 0 · Answer 5 · answered Feb 25 '14 at 11:25

0

Try the REPLACE method of sql server from here

like this

REPLACE(phone_number,' ','')

answered Feb 25 '14 at 11:25

Vikas Rana

score 0 · Answer 6 · answered Feb 25 '14 at 12:07

0

We can do like this aslo

DECLARE @XML VARCHAR(MAX) =  '07595 8832 36'
select REPLACE(RTRIM(LTRIM(@XML)),' ','')

answered Feb 25 '14 at 12:07

mohan111

explain why you need to trim when replacing the spaces – t-clausen.dk Feb 25 '14 at 14:21

6 Answers6