tag:blogger.com,1999:blog-18371456.post114195749393984966..comments2024-03-26T00:07:20.400-05:00Comments on The Db2 Portal Blog: Returning Only Numeric DataCraig S. Mullinshttp://www.blogger.com/profile/17077237739217901780noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-18371456.post-68731886163291041242012-04-10T23:41:28.658-05:002012-04-10T23:41:28.658-05:00Select * from table where VAR like '[0-9][0-9]...Select * from table where VAR like '[0-9][0-9][0-9][0-9][0-9]'Karthik Rnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-13048327994262213982011-08-17T06:19:26.962-05:002011-08-17T06:19:26.962-05:00This is optimal, easily and quickly
SELECT *
FROM...This is optimal, easily and quickly<br /><br />SELECT *<br />FROM Table<br />WHERE TRANSLATE(Field, <br /> ' ', <br /> '0123456789,. '<br /> ) = '' <br />AND Field <> ''Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-36768941259996829212011-07-26T10:50:49.626-05:002011-07-26T10:50:49.626-05:00UCASE and LCASE worked also LIKE '%F_F_F_F_F%...UCASE and LCASE worked also LIKE '%F_F_F_F_F%'. I tested the other ways and they don't work, I am using it in a credit card field which should contain ONLY encrypted dataAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-35509511933326066822010-11-04T00:51:00.071-05:002010-11-04T00:51:00.071-05:00here is a nice way :
select * from
tablename
wh...here is a nice way : <br />select * from <br />tablename <br />where <br />HEX(VAR) LIKE '%F_F_F_F_F%'natannoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-44368663686742351532010-08-06T10:15:59.906-05:002010-08-06T10:15:59.906-05:00It is Shame that DB2 does not have IS NUMERIC chec...It is Shame that DB2 does not have IS NUMERIC checkPhoenixhttps://www.blogger.com/profile/05699027415434334026noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-24912342618932755902009-09-04T07:45:09.967-05:002009-09-04T07:45:09.967-05:00the above works for DB2the above works for DB2Unknownhttps://www.blogger.com/profile/14417383877676005383noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-30740946377635629182009-09-04T07:40:53.253-05:002009-09-04T07:40:53.253-05:00SELECT * FROM table
WHERE TRANSLATE(TRANSLATE(fiel...SELECT * FROM table<br />WHERE TRANSLATE(TRANSLATE(field, '0123456789, ', '0000000000 '), ' ', '0123456789') <> ' '<br />AND field <> ''Unknownhttps://www.blogger.com/profile/14417383877676005383noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-54495487591821096192007-08-15T08:10:00.000-05:002007-08-15T08:10:00.000-05:00What in case you have XXXXX in ZIpcode instead of ...What in case you have XXXXX in ZIpcode instead of Numerics.<BR/>What Query you use in design view?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-1148331814664601282006-05-22T16:03:00.000-05:002006-05-22T16:03:00.000-05:00I don't know if the following helps :To check for ...I don't know if the following helps :<BR/>To check for the any numeric data (does not currently support commas)<BR/>SELECT C1<BR/>,case <BR/>when rtrim(ltrim(c1)) in ('-','+','-.','+.','','.') <BR/> then 'Non-Numeric'<BR/>when replace(translate(rtrim(ltrim(c1)),'x ',' 0123456789'),' ','') in ('-','+','-.','+.','','.')<BR/> then 'Numeric'<BR/>else 'Non-Numeric'<BR/>end<BR/>FROM TEMP1<BR/>;Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-1148277280248544892006-05-22T00:54:00.000-05:002006-05-22T00:54:00.000-05:00-- You could also use the following (var4 is 4 byt...-- You could also use the following (var4 is 4 bytes):<BR/><BR/>SELECT * <BR/>FROM TABLENAME<BR/>WHERE TRANSLATE(VAR4,'##########','1234567890') = '####'<BR/>;<BR/><BR/>-- Scott ShatskyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-1146891120960515122006-05-05T23:52:00.000-05:002006-05-05T23:52:00.000-05:00This example shows a good example where a stored p...This example shows a good example where a stored procedure comes in handy. Like with Oracle, you'd write a Function which takes a value and returns a BOOL telling you whether a column is numeric or not.<BR/><BR/>So then your SQL would look like:<BR/><BR/>SELECT <BR/> * <BR/>FROM <BR/> TABLENAME<BR/>WHERE <BR/> IS_NUMERIC(VAR) = TRUE;<BR/><BR/>Writing this in normal SQL would be slow and painful. There really is no clean and effective way to handle this situtation.<BR/><BR/>- Richard BurtonAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-18371456.post-1144288015932404852006-04-05T20:46:00.000-05:002006-04-05T20:46:00.000-05:00Good idea. That SQL should work if the only charac...Good idea. That SQL should work if the only characters are numbers or the letters A-Z. If there are other options (such as punctuation or special characters) then it would not work as UCASE and LCASE only translate A-Z (or a-z).Craig S. Mullinshttps://www.blogger.com/profile/17077237739217901780noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-1144280884431413722006-04-05T18:48:00.000-05:002006-04-05T18:48:00.000-05:00You could use the following SQL:SELECT *FROM TABLE...You could use the following SQL:<BR/>SELECT *<BR/>FROM TABLENAME<BR/>WHERE UCASE(VAR) = LCASE(VAR);<BR/><BR/>/tage gejlAnonymousnoreply@blogger.com