Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
SQL WHERE clause question.....
has no ratings.
Published: 02 Jul 2013
Revised: 15 Jul 2013 - 1592 days ago
Last viewed on: 22 Nov 2017 (3152 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL WHERE clause question..... Published by: Chris Proctor on 02 Jul 2013 view comments(5)

Hello! A co-worker is trying to to select records where a field has a numeric value, but can contain some blanks. So, he needs to be able to select records where the field contains any 1-9 numeric values and possibly blanks.

Can this be done? If you have any suggestions I'm sure he'd appreciate hearing them.

Thanks!

Chris

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: SQL WHERE clause question.....
Posted: 4 years 4 months 23 days 8 hours 54 minutes ago
Edited: Wed, 03 Jul, 2013 at 07:10:19 (1604 days ago)

So what you want is the SQL equivalent of

IF %CHECK(' 0123456789':mystring) = 0

Unfortunately, ANSI and DB2 SQL don't have such a function.

You could create your own scalar function, though. I wouldn't even try to do it in SQL; just call RPG and use %CHECK(). Make it flexible by passing both comparator and base strings as parameters. SQL doesn't have booleans (indicators), so you'll want to return the integer position value given by %CHECK. If it's 0, it wasn't found; otherwise, you have the position of the first non-matching character.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL WHERE clause question.....
Posted: 4 years 4 months 23 days 5 hours 49 minutes ago

It sounds like he/she is looking for a Filter/Compress routine. I have those in COZTOOLS but for within RPG, not SQL. I know some people have written SQL Functions to compress out non-Alphanumeric characters from a field and return only the "good" data. You'd have to do the same to get the results they expect.

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: SQL WHERE clause question.....
Posted: 4 years 4 months 23 days 3 hours 23 minutes ago
Edited: Wed, 03 Jul, 2013 at 12:42:24 (1604 days ago)

I know it's ugly, but you could test each character for a blank or a numeric digit:

Select * from file where sst(field,1,1) in (' ','0','1','2','3','4','5','6','7','8','9') and sst(field,2,1) in (' ','0','1','2','3','4','5','6','7','8','9') and sst(field,3,1) in (' ','0','1','2','3','4','5','6','7','8','9') and sst(field,4,1) in (' ','0','1','2','3','4','5','6','7','8','9') and ...

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: SQL WHERE clause question.....
Posted: 4 years 4 months 22 days 4 hours 16 minutes ago
Edited: Thu, 04 Jul, 2013 at 11:53:32 (1603 days ago)

OK, thought of a much better method:

Select * from file where translate(field,'          ','0123456789') = ' '

This will select the records where the field contains only numbers and/or blanks.  If you actually want only numbers 1 - 9 (not including 0), then omit the zero from the list of numbers in the translate function above.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: SQL WHERE clause question.....
Posted: 4 years 4 months 11 days 5 hours 1 minutes ago

WHERE LENGTH(TRIM(TRANSLATE(TRANSLATE(TRIM(the_field)
        ,'X',' '), ' ', '0123456789'))) = 0

In this code, if the result is zero then there were only numeric values in the field.