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.
Using regular expressions
SELECT * from table where id REGEXP '^(1+|2+|3+|4+|5+|6+|7+|8+|9+)$';
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).
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)
.
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'