Regex in sqlite

Question

i have been thinking about this a lot.

So i wanna create a table which contains a password. The password should at least be 6 chars long a contain minimum 2 numbers.

My version was:

create table User (
  passwort varchar(80) not null check (length(passwort) >= 6 and passwort like '%[0-9]%[0-9]%')
);

The Problem with this approach is that the password has to contain [0-9] twice instead of the actual numbers. Does anyone know how to get rid of that problem ?

Thanks in advance.

What if you use this pattern: `^.*?\d.*?\d.*?$` and then check the length of the string? — npinti, Feb 17 '20 at 13:07
Does this answer your question? [How do I use regex in a SQLite query?](https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query) — Mark Benningfield, Feb 17 '20 at 13:42
SQLite does not support regular expressions with the `LIKE` operator. — Mark Benningfield, Feb 17 '20 at 13:43

score 0 · Answer 1 · answered Feb 17 '20 at 13:07

0

How about .*?\d.*?\d.*?

This ensures that between zero or more characters (including digits), there must be 2 digits.

answered Feb 17 '20 at 13:07

Robo Mop

3,485
1
10
23

score 0 · Answer 2 · answered Feb 17 '20 at 13:11

0

While I still recommend you split the work in 2 as per my comment, ie.

Check the length of the string.
Use the actual expression to check if the string contains 2 numbers.

You can use the following expression: ^(?=.{6,}).*?\d.*?\d.*?$. What is does is that it looks ahead for a minimum of 6 characters and then checks that the string is made up from 2 numbers, which can be separated by 0 or more characters.

An example of the expression is available here.

answered Feb 17 '20 at 13:11

npinti

51,780
5
72
96

i just tried your suggested pattern but unfortunately, that does not work too. – PersianAssassin Feb 17 '20 at 13:21
@PersianAssassin: Do you have any examples wherein my answer fails please? – npinti Feb 17 '20 at 13:29

Regex in sqlite

2 Answers2