CODEWITHSUNDEEP
mysql
E_Blue
E_Blue

Reputation: 1151

Upper case and lower case query filter

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?

Upvotes: 0

Views: 1239

Answers (1)

Bohemian
Bohemian

Reputation: 425043

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)))

Upvotes: 1

Related Questions