CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ap9_Jacka
Ap9_Jacka

Reputation: 21

Replace Spaces in Telephone Column

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

Upvotes: 0

Views: 252

Answers (6)

LuisR9
LuisR9

Reputation: 116

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),''), ' ','')

Upvotes: 1

mohan111
mohan111

Reputation: 8865

We can do like this aslo 

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

Upvotes: 0

Vikas Rana
Vikas Rana

Reputation: 1999

Try the REPLACE method of sql server from here

like this

REPLACE(phone_number,' ','')

Upvotes: 0

Developerzzz
Developerzzz

Reputation: 1126

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]

Upvotes: 0

jpw
jpw

Reputation: 44891

Like this:

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

Upvotes: 1

GriGrim
GriGrim

Reputation: 2921

REPLACE (phone_number, ' ', '')

Upvotes: 1

Related Questions