MySQL - I want to check at what index in a string there's a number

Question

So I've found that I can use SELECT LOCATE(1,"TEST1"); and it returns 5 correctly.

What I want to do though is add this as a constraint to my table, so TEST1 will be changing and the goal is to check that the 5th position is always a number. This works, but I was wondering whether there is a much easier method to add in 0 until 9 without having to write this line 10 times.

Right now my constraint looks like this :

ALTER TABLE Accommodatie
ADD CONSTRAINT acode_checker
CHECK (LENGTH(accommodatie_code) = 5);

So now I have to add the check for the 5th position, any tips?

Possible duplicate of https://stackoverflow.com/questions/70335322/how-to-give-a-value-a-specific-length/70337040#70337040 — Bill Karwin, Dec 16 '21 at 18:43
Is the requirement that the 5th character is a digit, or a number (i.e. at least one digit)? The distinction is important if there are ever any additional restraints. Are there any restrictions on the first 4 characters? In particular, are they allowed or not allowed to be digits? — outis, Dec 16 '21 at 21:41
Does this answer your question? [how to Allow only alpha numeric values in mysql](https://stackoverflow.com/q/57196488/90527) — outis, Dec 16 '21 at 21:50

score 1 · Answer 1 · edited Dec 16 '21 at 21:06

Check out the MySQL REGEXP documentation

Something like the below should work for you. REGEXP returns 1 here if accomodatie_code matches the given pattern which is any 4 characters (each . is a single character), any digit in the 5th character space ([0-9]), the ^ tells it to start matching from the beginning of the string.

ALTER TABLE Accommodatie
ADD CONSTRAINT acode_checker
CHECK (LENGTH(accommodatie_code) = 5 
    AND accommodatie_code REGEXP '^....[0-9]' = 1)

MySQL - I want to check at what index in a string there's a number

1 Answers1