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
 

 

Validate for Alphanumeric

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

New User


Joined: 21 Nov 2005
Posts: 18

PostPosted: Fri Apr 24, 2009 4:09 pm    Post subject: Validate for Alphanumeric
Reply with quote

I have to list all rows in a table that have values in a CHAR(10) column containing any character that is not alphanumeric.
Any bright ideas on how to code this in a Select Statement, or am I missing/forgetting something basic?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Apr 24, 2009 4:56 pm    Post subject:
Reply with quote

as soon as you tell us what is NOT alphanumeric, a solution maybe forthcoming.
Back to top
View user's profile Send private message
MCEVOY

New User


Joined: 21 Nov 2005
Posts: 18

PostPosted: Fri Apr 24, 2009 5:04 pm    Post subject: Reply to: Validate for Alphanumeric
Reply with quote

Anything other than the latters A-Z and the numbers 0-9.
Thanks for your interest.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 24, 2009 5:04 pm    Post subject:
Reply with quote

Code:


SELECT COL FROM
(
SELECT COL,

CASE TRANSLATE(UPPER(COL), ' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ' '
THEN 1
ELSE 0
END AS VAL

FROM TABLE
) AS TEMP
WHERE VAL = 1 ;


this will give you only those values which are in range A - Z ...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 24, 2009 5:06 pm    Post subject:
Reply with quote

Oops .. i missunderstud your last post ... do the changes accordingly ..
Back to top
View user's profile Send private message
MCEVOY

New User


Joined: 21 Nov 2005
Posts: 18

PostPosted: Fri Apr 24, 2009 5:09 pm    Post subject: Reply to: Validate for Alphanumeric
Reply with quote

Brilliant. I get the idea.
Thanks a million.
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. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
This topic is locked: you cannot edit posts or make replies. Alphanumeric to Numeric move on UNIX mistah kurtz COBOL Programming 16 Wed Jul 27, 2016 8:47 pm
No new posts alphanumeric sequence generation sivaprasad.gadhi COBOL Programming 6 Wed Mar 30, 2016 3:03 am
No new posts alphanumeric sequence generation sivaprasad.gadhi DFSORT/ICETOOL 8 Mon Mar 21, 2016 1:09 am
No new posts To determine the range on a Alphanume... Prasanth Kumar COBOL Programming 3 Mon Feb 15, 2016 9:35 am


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