IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

SQl Query


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: :: View next topic  
Author Message
sanjaykavita

New User


Joined: 13 Aug 2009
Posts: 6
Location: Pune

PostPosted: Thu Jul 24, 2014 5:19 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2503
Location: NY,USA

PostPosted: Thu Jul 24, 2014 8:16 pm
Reply with quote

Try below,

Code:
SELECT STRIP(CHAR(REPLACE('9 4444444',' ',''),20))                               
              FROM SYSIBM.SYSDUMMY1
Back to top
View user's profile Send private message
sanjaykavita

New User


Joined: 13 Aug 2009
Posts: 6
Location: Pune

PostPosted: Thu Jul 24, 2014 8:49 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2503
Location: NY,USA

PostPosted: Fri Jul 25, 2014 12:15 am
Reply with quote

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
View user's profile Send private message
sanjaykavita

New User


Joined: 13 Aug 2009
Posts: 6
Location: Pune

PostPosted: Fri Jul 25, 2014 12:47 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jul 25, 2014 3:47 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2503
Location: NY,USA

PostPosted: Fri Jul 25, 2014 8:02 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
No new posts DFSORT Output file order query DFSORT/ICETOOL 2
No new posts Query on JCL DFSORT/ICETOOL 5
No new posts DB2 Query runs very long DB2 21
No new posts Query to Truncate value in table DB2 2

Back to Top