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
 

 

Selecting the criteria based on alphabets a to z

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Tue Jan 01, 2013 12:52 am    Post subject: Selecting the criteria based on alphabets a to z
Reply with quote

Hi All,

Is there a way to write a table to select all the columns which have alphabets from A-Z
Example:
Sample table data:
Slno. Emp_Name Emp_id
1 RRR 101
2 SSS A12
3 TTT 102
4 XXX 2B3
5 YYY 103
6 ZZZ 3FF

So my result should be
2 SSS A12
4 XXX 2B3
6 ZZZ 3FF

I tried running the below query but it didn't fetch any result
SELECT * FROM EMP_TABLE WHERE EMP_ID LIKE '%[A-Z]%';

Could you please help me on this,

Also, Happy new year to everyone!!
Back to top
View user's profile Send private message

ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Tue Jan 01, 2013 1:03 am    Post subject:
Reply with quote

Just to add more to my note, I can achieve it by using the below query but its more lengthy and confusing. It would be great if I could get a quicker and smaller query

Quote:
SELECT * FROM EMP_TABLE WHERE
((EMP_ID LIKE '%A%') OR
(EMP_ID LIKE '%B%') OR
(EMP_ID LIKE '%C%') OR
(EMP_ID LIKE '%D%') OR
(EMP_ID LIKE '%E%') OR
(EMP_ID LIKE '%F%') OR
(EMP_ID LIKE '%G%') OR
(EMP_ID LIKE '%H%') OR
(EMP_ID LIKE '%I%') OR
(EMP_ID LIKE '%J%') OR
(EMP_ID LIKE '%K%') OR
(EMP_ID LIKE '%L%') OR
(EMP_ID LIKE '%M%') OR
(EMP_ID LIKE '%N%') OR
(EMP_ID LIKE '%O%') OR
(EMP_ID LIKE '%P%') OR
(EMP_ID LIKE '%Q%') OR
(EMP_ID LIKE '%R%') OR
(EMP_ID LIKE '%S%') OR
(EMP_ID LIKE '%T%') OR
(EMP_ID LIKE '%U%') OR
(EMP_ID LIKE '%V%') OR
(EMP_ID LIKE '%W%') OR
(EMP_ID LIKE '%X%') OR
(EMP_ID LIKE '%Y%') OR
(EMP_ID LIKE '%Z%'))
Back to top
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Tue Jan 01, 2013 2:57 am    Post subject:
Reply with quote

Ravi,

You can use TRANSLATE function in DB2.


Code:
SELECT * FROM EMP_TABLE WHERE translate(EMP_ID , ' ','1234567890') <> ' ' ;
Back to top
View user's profile Send private message
ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Tue Jan 01, 2013 3:11 am    Post subject:
Reply with quote

Thanks Sai,
This worked for me.

Happy New Year.

Regards,
Ravi
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
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
This topic is locked: you cannot edit posts or make replies. Job to wait and continue based on reply yugendran COBOL Programming 5 Wed Oct 05, 2016 1:46 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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