Why REGEXP_COUNT returns a wrong value?

[Carpiem]

Hello all,

I'm puzzled why REGEXP_COUNT returns weird results, for example take this query:

select regexp_count('1','[A-Z]')
from SYSIBM.SYSDUMMY1 

Regular expression [A-Z] means it accepts a letter from A to Z. But 1 is no letter, and the result of this query is 2 which is wrong!
This happens if I try to use this SQL in a RPG program or via STRSQL interactive job.

However, in my emulator there is an option "Run SQL Scripts" which opens a SQL client application. When I execute the SQL in this client the result is 0 which is correct!
REGEXP_COUNT works like a charm!

When using this SQL client the job is 123456/QUSER/QZDASOINIT.

Why in job 123456/QUSER/QZDASOINIT it works perfectly but when I try to use in another job I get funny results? The CCSID is the same in both jobs -> 500.

Any help is very welcome. Regular expressions would solve a important problem for our business.


Thank you in advance

[jtaylor___]

I get 0 in Run SQL Scripts & 5250 STRSQL.

My only suggestion would be to compare your SQL settings between Run SQL Scripts and embedded SQL/STRSQL.

[Carpiem]

Thank you for the input jtaylor.

I found the problem, it happens because the CCSID of the job is 500. When I change the CCSID to 037 it works correctly.

Do you know another solution without changing the job CCSID (it could imply other problems), like converting the regular expression from 500 to 037?