Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQl Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: SQl Query
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

Senior Member


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

PostPosted: Thu Jul 24, 2014 8:16 pm    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Fri Jul 25, 2014 12:15 am    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Jul 25, 2014 3:47 pm    Post subject:
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

Senior Member


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

PostPosted: Fri Jul 25, 2014 8:02 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us