This is my first time using H2 and I'm trying to select only the records that DO NOT have a numerical ID (e.g. H27384WK would be valid, but 398453 would not). After researching this online, I couldn't find any solutions for this and none of the functions mentioned here seem to be helpful. How could I achieve this functionality?
H2 determining if String value is numeric
|
Refresh |
April 2019 |
|
Views |
1.1k time |
2 answers
2
An other solution is with a combination of UPPER and LOWER
WHERE NOT (LOWER(id) = UPPER(id))
You might need to add additional conditions for NULL and/or empty values.
2
One way to do this is with translate:
where translate(id, 'A0123456789', 'A') <> ''
You might need to check for NULL explicitly (I'm not sure if H2 treats empty strings as NULL the way that Oracle does):
where translate(id, 'A0123456789', 'A') is not null