Clever Geek Handbook

How to display numbers that are similar to 11111 or similar in MS sql

I am trying to display numbers from an ID column that should have the same number.

Like 11111 or 2222 or 33333

select * from table where id like '1111'

- this will give me 1 value, but there are many values ​​like 1111111111 or 111111 that I am not sure about as there are over 100k entries.

+3


source to share


4 answers


Using regular expressions



SELECT * from table where id REGEXP '^(1+|2+|3+|4+|5+|6+|7+|8+|9+)$';
+2


source


You can do this with regular expressions like:

where id regex '^((1+)|(2+)|(3+)|(4+)|(5+)|(6+)|(7+)|(8+)|(9+)|(0+))$'


After testing , this requires an additional set of parentheses (just added).

+2


source


If the numbers must match the first, you can write the first digit and then use the backreference:

^([0-9])\1+$

This will work if you have LIB_MYSQLUDF_PREG installed , which imports the PCRE library (according to Regular-expressions.info ). And the syntax will be PREG_RLIKE('/^([0-9])\\1+$/i', col)

.

+1


source


Use the wildcard%

If you want to include numbers like 2341111, 1111342 or 23111154, etc.

SELECT *
FROM table
WHERE id LIKE '%1111%'

If you only want numbers with the same numeric value 1111 or 1111111 or 11111111 etc.

SELECT *
FROM table
WHERE id LIKE '1%11%1'
0


source






More articles:


All Articles