Numeric data not valid error in SQL using Regex

Question

I have the below SQL that is throwing a "Numeric data not valid" error when executed:

SELECT WORKKEY , WRKTITLE, INT(SUBSTR(WRKTITLE , 26, 8) ) 
FROM IACDTAA.IACWTL
WHERE 
REGEXP_LIKE(TRIM(WRKTITLE), '^[0-9]{1,4} ARTX LU 079 PUBLDTE=[0-9]{8}       A$')
AND WORKKEY IN (SELECT WORKKEY FROM IACDTAA.IACNTC WHERE NTFCTLKEY = 195586   )
AND INT(SUBSTR(WRKTITLE , 26, 8) ) > 0 ;

The problem seems to be the comparison condition INT(SUBSTR(WRKTITLE , 26, 8) ) > 0

Basically, the regular expression is trying to find out records where the WRKTITLE field has values such as :

0048 ARTX LU 079 PUBLDTE=19880708       A                                                 
0060 ARTX LU 079 PUBLDTE=20120101       A

But to me it appears that the SQL is actually selecting records where the WRKTITLE is not in this format and that results in the INT function to be applied to "non-numeric" values.

What could be going wrong here ?

Just make sure the index is correct in substr, I feel like it should be int(substr(wrktitle, 25, 8) and not 26 — Sahil Patel
– Sahil Patel, Commented May 17, 2023 at 10:21
@nbk , Sorry, could you please elaborate ? I tried copying your SQL but that also doesn't work. — Theju112
– Theju112, Commented May 17, 2023 at 11:56
@nbk, Just to clarify, When I further filter out the records using some more field values to point to the exact records, the reg expression works exactly as needed. So it makes me believe that the regexp is being applied to some other records. — Theju112
– Theju112, Commented May 17, 2023 at 12:12
it is quite easy youhave a fixed number of spaces in your regular expression, if your column has less or more spaces before the A the REGEX_Like produces a false changing it to dbfiddle.uk/O5kDDCYJ would work for any number of spaces, patterns in regular expression have to match exactly, else no row will be recognized, you should test your regular expression for example here regex101.com — nbk
– nbk, Commented May 17, 2023 at 12:22

Theju112 · Accepted Answer · 2023-05-17 13:55:43Z

0

Okay, so there seems to be something about casting which I dont know, but the below works:

SELECT WORKKEY , WRKTITLE, SUBSTR(WRKTITLE , 26, 8) 
FROM IACDTAA.IACWTL
WHERE 
REGEXP_LIKE(TRIM(WRKTITLE), '^[0-9]{4} ARTX LU 079 PUBLDTE=[0-9]{8}[ ]{7}A$')
AND WORKKEY IN (SELECT WORKKEY FROM IACDTAA.IACNTC WHERE NTFCTLKEY = 195586   )
AND SUBSTR(WRKTITLE , 26, 8) > '0'

Instead of converting to INT , just doing a plain comparison using a char value works! Now if someone could point out why, it would be great!

answered May 17, 2023 at 13:55

Theju112

16710 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Barbara Morris Over a year ago

In the SQL reference, when it is talking about the precedence of NOT OR AND, it says "The order in which operators at the same precedence level are evaluated is undefined to allow for optimization of search conditions." So if you have X and Y and Z, it doesn't necessarily check X first and stop checking the other conditions if X is false.

Theju112 Over a year ago

wow thank you so much @BarbaraMorris! that's the exact answer I was searching for!! So, the INT() must have been used on values not matching the REGEX. I tried using a CTE using WITH to filter out records matching the REGEX, hoping the CTE would be processed first but even that failed. So would you have any suggestions, please ?

John Y Over a year ago

Could you edit your question to include your attempt at using a CTE? I wouldn't remove anything that's already in your question, just add something between "INT function applied to nonnumeric values" and "what's going wrong" which says "I also tried the following, which didn't work either" and put in the exact code you used?

John Y Over a year ago

As for why doing a straight character comparison "works" for your situation: It's because '0' through '9' are very near the top of the range of single-byte values in EBCDIC, and your data happens to cooperate. '0' is higher than any of the letters, punctuation, or blank space; so if byte 26 is a letter, punctuation, or blank space, it will be less than '0'. You can construct data that makes the character comparison fail. For example, if byte 26 happens to be '1', then even if byte 27 is a letter, the substring will compare greater than '0'.

Collectives™ on Stack Overflow

Numeric data not valid error in SQL using Regex

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related