View previous topic :: View next topic
|
Author |
Message |
royalchm
New User
Joined: 15 Nov 2007 Posts: 29 Location: Bangalore
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
>= x'ef' ?? remembering that 0 is x'f0' |
|
Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
'1prino666' is between '0' and '9'
and > x'ef' doesn't work in unicode. |
|
Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
royalchm
New User
Joined: 15 Nov 2007 Posts: 29 Location: Bangalore
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Now can you run the same against sysibm.sysdummyU ? and is it relevant ? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
I wasn't on my best yesterday
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 |
|
|
royalchm
New User
Joined: 15 Nov 2007 Posts: 29 Location: Bangalore
|
|
|
|
GuyC wrote: |
I wasn't on my best yesterday
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
|