H2 determining if String value is numeric

Refresh

April 2019

Views

1.1k time

1

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?

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