IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Check numeric data in DB2


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

New User


Joined: 15 Nov 2007
Posts: 29
Location: Bangalore

PostPosted: Mon Mar 21, 2011 6:29 pm
Reply with quote

i have a requirment saying that in an alphanumeric field need to fetch only numeric values.
Ex:
field1 have 1,2,3,a,b,c,
i need only numeric value. How we will fetch only numeric data from the table using query?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Mar 22, 2011 1:15 am
Reply with quote

>= x'ef' ?? remembering that 0 is x'f0'
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Tue Mar 22, 2011 1:45 am
Reply with quote

royalchm wrote:
i have a requirment saying that in an alphanumeric field need to fetch only numeric values.
Ex:
field1 have 1,2,3,a,b,c,
i need only numeric value. How we will fetch only numeric data from the table using query?


Code:
where mycol between '0' and '9'

Sheesh, that was really difficult...

Please remove DB2 from the skills in your profile, as you obviously do not have any!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Mar 22, 2011 2:22 pm
Reply with quote

'1prino666' is between '0' and '9'
and > x'ef' doesn't work in unicode.
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Tue Mar 22, 2011 2:26 pm
Reply with quote

GuyC wrote:
'1prino666' is between '0' and '9'
and > x'ef' doesn't work in unicode.

The TS never mentioned Unicode, nor did he use values with a length exceeding a single character!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Mar 22, 2011 2:41 pm
Reply with quote

He never mentioned EBCDIC either.
In DB2 nowadays neither can be assumed and a solution containing hex values should consider both.

Who says 1,2,3,a,b,c, are values. Maybe he means possible characters that are part of the field1.
Not all TS are as eloquent. Sometimes when the answer is too simple, we have misunderstood the (intended) question.
Back to top
View user's profile Send private message
royalchm

New User


Joined: 15 Nov 2007
Posts: 29
Location: Bangalore

PostPosted: Tue Mar 22, 2011 6:32 pm
Reply with quote

GuyC wrote:
He never mentioned EBCDIC either.
In DB2 nowadays neither can be assumed and a solution containing hex values should consider both.

Who says 1,2,3,a,b,c, are values. Maybe he means possible characters that are part of the field1.
Not all TS are as eloquent. Sometimes when the answer is too simple, we have misunderstood the (intended) question.


I have tried the same in the following way:

I put below check in the where clause
Code:
UCASE(field1) = LCASE(field1) ----> both will be same only for numeric.

I have run the same query in SPUFI and it will return only numeric data.
Please let me know any problem for the above.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Mar 22, 2011 6:46 pm
Reply with quote

special characters (/,;$+-... and spaces ) - if any - could be a problem, otherwise I don't see a problem with it.

another solution could be
Code:
translate(col, 'X          ',' 0123456789') = ' '
this would only allow numbers, no special sign or spaces
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Mar 22, 2011 7:21 pm
Reply with quote

ran this simple sql,
Code:

SELECT HEX(UCASE(X'000102030405060708090A0B0C0D0E0F'))
      ,HEX(UCASE(X'101112131415161718191A1B1C1D1E1F'))
      ,HEX(UCASE(X'202122232425262728292A2B2C2D2E2F'))
      ,HEX(UCASE(X'303132333435363738393A3B3C3D3E3F'))
      ,HEX(UCASE(X'404142434445464748494A4B4C4D4E4F'))
      ,HEX(UCASE(X'505152535455565758595A5B5C5D5E5F'))
      ,HEX(UCASE(X'606162636465666768696A6B6C6D6E6F'))
      ,HEX(UCASE(X'707172737475767778797A7B7C7D7E7F'))
      ,HEX(UCASE(X'808182838485868788898A8B8C8D8E8F'))
      ,HEX(UCASE(X'909192939495969798999A9B9C9D9E9F'))
      ,HEX(UCASE(X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'))
      ,HEX(UCASE(X'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'))
      ,HEX(UCASE(X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF'))
      ,HEX(UCASE(X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'))
      ,HEX(UCASE(X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF'))
      ,HEX(UCASE(X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'))
FROM SYSIBM.SYSDUMMY1

which provided these results
Code:

000102030405060708090A0B0C0D0E0F
101112131415161718191A1B1C1D1E1F
202122232425262728292A2B2C2D2E2F
303132333435363738393A3B3C3D3E3F
404142434445464748494A4B4C4D4E4F
505152535455565758595A5B5C5D5E5F
606162636465666768696A6B6C6D6E6F
707172737475767778797A7B7C7D7E7F
80C1C2C3C4C5C6C7C8C98A8B8C8D8E8F    <<special attention here
90D1D2D3D4D5D6D7D8D99A9B9C9D9E9F    <<and here
A0A1E2E3E4E5E6E7E8E9AAABACADAEAF
B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF
C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF
D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF
E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF
F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF


and the converse sql:
Code:

SELECT HEX(LCASE(X'000102030405060708090A0B0C0D0E0F'))
      ,HEX(LCASE(X'101112131415161718191A1B1C1D1E1F'))
      ,HEX(LCASE(X'202122232425262728292A2B2C2D2E2F'))
      ,HEX(LCASE(X'303132333435363738393A3B3C3D3E3F'))
      ,HEX(LCASE(X'404142434445464748494A4B4C4D4E4F'))
      ,HEX(LCASE(X'505152535455565758595A5B5C5D5E5F'))
      ,HEX(LCASE(X'606162636465666768696A6B6C6D6E6F'))
      ,HEX(LCASE(X'707172737475767778797A7B7C7D7E7F'))
      ,HEX(LCASE(X'808182838485868788898A8B8C8D8E8F'))
      ,HEX(LCASE(X'909192939495969798999A9B9C9D9E9F'))
      ,HEX(LCASE(X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'))
      ,HEX(LCASE(X'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'))
      ,HEX(LCASE(X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF'))
      ,HEX(LCASE(X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'))
      ,HEX(LCASE(X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF'))
      ,HEX(LCASE(X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'))
FROM SYSIBM.SYSDUMMY1
;


which provided these results.
Code:

000102030405060708090A0B0C0D0E0F
101112131415161718191A1B1C1D1E1F
202122232425262728292A2B2C2D2E2F
303132333435363738393A3B3C3D3E3F
404142434445464748494A4B4C4D4E4F
505152535455565758595A5B5C5D5E5F
606162636465666768696A6B6C6D6E6F
707172737475767778797A7B7C7D7E7F
808182838485868788898A8B8C8D8E8F
909192939495969798999A9B9C9D9E9F
A0A1A2A3A4A5A6A7A8A9AAABACADAEAF
B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF
C0818283848586878889CACBCCCDCECF       <<<conversely here
D0919293949596979899DADBDCDDDEDF       <<<and here
E0E1A2A3A4A5A6A7A8A9EAEBECEDEEEF
F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF


i could have edited the display to make it easier to read,
but then,
I was afraid some would make assumptions.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Mar 22, 2011 8:21 pm
Reply with quote

Now can you run the same against sysibm.sysdummyU ? and is it relevant ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Mar 22, 2011 9:03 pm
Reply with quote

GuyC,

little prickly today, huh?

the ts starts from a char column.
so,he can have any of the 256 (in EBCDIC) values for each byte (char) of his column.

I just wanted to show that only the 26 english alpha are changed with ucase and lcase.

the german umlautete char are not affected (CODOEO4A5A6A).

if i took the time to peform against UNICODE or any other char set,
would still be the same. only the alpha char for that locale.

so is it relevant? depends on if the ts wants to do it correctly or not?
ts was happy with his ucase/lcase formulation.

instead of the ucase/lcase silliness, a properly constructed translate
(as you posted - not that I would use that
why not simply?
Code:

translate(col,'          ','0123456789')= ' '

would perform the work of determining if there existed anything except
0 thru 9 in the column.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Mar 23, 2011 1:12 pm
Reply with quote

woke up about 0230 this morning,
after finally getting over my personal issues,
and realized why
Code:

translate(col, 'X          ',' 0123456789') = ' '


is the way to go.

obviously, i stepped-in-it-big-time, yesterday.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Mar 23, 2011 2:44 pm
Reply with quote

I wasn't on my best yesterday icon_smile.gif
The space to X in the translate is just to disallow spaces in the input, but I guess you already figured it out
Back to top
View user's profile Send private message
royalchm

New User


Joined: 15 Nov 2007
Posts: 29
Location: Bangalore

PostPosted: Wed Mar 23, 2011 6:01 pm
Reply with quote

GuyC wrote:
I wasn't on my best yesterday icon_smile.gif
The space to X in the translate is just to disallow spaces in the input, but I guess you already figured it out


Req: need to select only numeric data from a group of alphanumricc data.
eg:
Col1
-----
111
222
A01
A02
B01
333
I need only 111, 222, 333 all the other values shouldn't be selected.

I have put the query like below. pk means primary key

Code:
SELECT Col1 FROM Table1
WHERE pk1= 11111
           pk2 = 'abc'
           LCASE(Col1) = UCASE(Col1)


Can u put translate to the above query?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Mar 23, 2011 6:04 pm
Reply with quote

Quote:
I think TRANSLATE is a scalar function


and the problem?

and yes, you put the translate(...) = ' ' in a where condition.

why would you put it in the select?

really makes me feel good to participate in this one-way-community.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top