Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm
No new posts TBSARG - Deleting the search criteria... ISPFHerc TSO/ISPF 6 Thu Dec 14, 2017 8:18 am
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us