0

I have a varchar(40) column that saves an hexadecimal ID, I need that ID in upper case, like id_xxxx; where xxxx is an hexadecimal number 0-9 A-F The first 3 letters must be in lower case.

1) There's a way to get a list of the IDs where the hexadecimal number are not in upper case?

2) There's a way to make the database engine turn a wrong ID like ID_abc1 into id_ABC1?

Improve this question

1 Answer 1

Reset to default
1

Turn all values into the correct case with this expression:

concat('id_', upper(substr(myHexId, 4)))

See live demo in SQLFiddle.

To find all hex values that are not in upper case:

where substr(myHexId,4) != upper(substr(myHexId,4))

to find all incorrectly formatted values (either "id_" or hex not correct):

where myHexId != concat('id_', upper(substr(myHexId, 4)))
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

What happens if there's not a valid HEX number? Can I filter them and get a list?
What do you want exactly? Do you want to find all values that are not in the correct format? Not hex? I've added some options to the answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.