SQl Query

sanjaykavita · New User Joined: 13 Aug 2009 Posts: 6 Location: Pune

Customer_Phone column (TYPE = CHAR, Length = 20) from one of the table holds some data in such a way that it has spaces between it.
Please help me to find two suitable queries:-

1. To extract all such Customer_Phone having spaces between it.
(e.g. 94903 324, 9 4444444, 88 66 2222, 4545 3939, so I can say space may come anywhere between the numbers)

2. To remove those space and make them single number ( e.g. 94903324, 94444444, 88662222, 45453939)

I tried to use the TRIM and TRANSLAT functions but I was not able to achieve the desired result.

Rohit Umarjikar · Posted: Thu Jul 24, 2014 8:16 pm

Try below,

sanjaykavita · New User Joined: 13 Aug 2009 Posts: 6 Location: Pune

Rohit, Thanks for looking into this..
Can you please provide more details in your query, as I want to select all Customer_Phone having spaces between it. But in your query no where I can see the column name(Customer_Phone). However I tried using it by amending it in below way,,but did not work.

SELECT MOBILE_PHONE_NO
( STRIP(CHAR(REPLACE('9 4444444',' ',''),20)))
FROM abcd.table
WITH UR;

Rohit Umarjikar · Posted: Fri Jul 25, 2014 12:15 am

Kavita,
why do you want to extract only those numbers who have a space(s) between them and then eventually remove them to look a whole number?

If you just place below query in place then you will not get ever a space(s) between a Phone number as you expect, is that what you want?

sanjaykavita · New User Joined: 13 Aug 2009 Posts: 6 Location: Pune

Rohit,
Actually those data (Customer_Phone having spaces between it) are incorrect and we need to correct those records. As the table is very big hence need a suitable query, first to get the list of those records and second to fix those records(by removing spaces between it and make them single number).

Hope it clarify my requirement.

GuyC · Posted: Fri Jul 25, 2014 3:47 pm

Rohit Umarjikar · Posted: Fri Jul 25, 2014 8:02 pm

Kavita,

You can use the direct query provided by GuyC as well as below query would give you all the phone numbers which has atleast one space between them.