View previous topic :: View next topic
|
Author |
Message |
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. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
Try below,
Code: |
SELECT STRIP(CHAR(REPLACE('9 4444444',' ',''),20))
FROM SYSIBM.SYSDUMMY1 |
|
|
Back to top |
|
|
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; |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
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?
Code: |
SELECT
( STRIP(CHAR(REPLACE(:MOBILE_PHONE_NO,' ',''),20)))
FROM abcd.table |
|
|
Back to top |
|
|
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. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
update abcd.table
set MOBILE_PHONE_NO = replace(MOBILE_PHONE_NO,' ','')
where not length(strip(MOBILE_PHONE_NO)) = length(strip(replace(MOBILE_PHONE_NO,' ',''))) |
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
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.
Code: |
select * from abcd.table
where LOCATE(' ',:MOBILE_PHONE_NO) <> 0 |
But for a solid fix you need to find the source and correct them as these queries may be expensive to use oftenly. |
|
Back to top |
|
|
|