CODEWITHSUNDEEP
sqlstringselectteradatateradata-sql-assistant
evdo_d
evdo_d

Reputation: 27

How to remove space from phone number (SQL)

I have phone numbers in the following format: 03 12345678 and 0412 3456789

I need to remove the space from the numbers so that I can join to another table where number format is 0312345679 and 04123456789. I do not want to update the table.

I have tried to run the following query for the home number format, but keep getting an error: 

SELECT 
REPLACE(p.Home_Phone_Num, ' ', '') AS Home_Num
FROM table

The error:

Syntax error: expected something between the 'SELECT' keyword and the 'REPLACE' keyword.

Thanks

Upvotes: 0

Views: 1363

Answers (2)

dnoeth
dnoeth

Reputation: 60482

To remove single characters there's no need for oReplace, use oTranslate instead:

oTranslate (p.Home_Phone_Num, ' ', '') AS Home_Num

This might also replace additional characters

oTranslate (p.Home_Phone_Num, ' -/()', '') AS Home_Num

Upvotes: 0

GMB
GMB

Reputation: 222582

This looks like a Teradata error message. This database does not have a replace() function - instead, you need oreplace():

select oreplace(p.Home_Phone_Num, ' ', '') as Home_Num from mytable

Upvotes: 1

Related Questions