MYSQL: Validate Input with Regular Expressions REGEX

Question

i have one question with regard to MYSQL. I want to create a function that is able to check whether an Input is given in a specific format.

The output should be in the following shape:

***x x (a) n (n) (n) (n)
with :
x = letters and numbers
n = numbers
a = letters
brackets = optional Values***

So my Solution until now is this (User 'Nick' helped me):

CREATE FUNCTION validate_number(testnumber VARCHAR(7))
RETURNS INT
DETERMINISTIC
RETURN testnumber REGEXP '^[[:alnum:]]{2}[[:alpha:]]?[[:digit:]]{1,4}$';

And this approach works for most cases. But when i enter a value that exceeds the possible amount of elements (max elements = 7) i get no result.

example:

validate_number('00A00002345')

=> no result.

Do you guys have an idea what the problem is?

Thank you very much in advance.

The problem is that you are hitting an exception due to the input parameters is longer than declared so the function code is not entering execution. Maybe the SQL Client you are using is not able to show it properly or the sql client you are working it is showing in a separate tab/window than the results. Try to change the regular expression or the store procedure in order to detect all what you want to consider included the length and by using a larger input parameter, i.e. VARCHAR(600) you should get it working — A. Lion
– A. Lion, Commented Jan 26, 2019 at 12:23
Yes, you are right. It was because of the restriciton i set with varchar(7). In fact, it was so easy! thank you very much! — Jimbo.Koen
– Jimbo.Koen, Commented Jan 26, 2019 at 12:38

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-26 12:30:42Z

1

you are actually pointing out the solution of the problem :)

just change VARCHAR(7) to something bigger VARCHAR(2000)

answered Jan 26, 2019 at 12:30

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-01-26 13:28:05Z

1

When I run your function, I get the error:

select validate_number('00A00002345')

Data too long for column 'testnumber' at row 1

You can add a length to the varchar.

CREATE FUNCTION validate_number (
    in_testnumber VARCHAR(32000)
)

Or, use text:

CREATE FUNCTION validate_number (
    in_testnumber text
)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN (in_testnumber REGEXP '^[[:alnum:]]{2}[[:alpha:]]?[[:digit:]]{1,4}$');
END;

answered Jan 26, 2019 at 13:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MYSQL: Validate Input with Regular Expressions REGEX

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related