How to wildcard numbers to a certain length in SQL

usermXQl1 0
Viewed 37

I currently have a column in a table that stores every number combination up to 6 digits followed by .lyr

Eg, 0.lyr ,1.lyr, 00.lyr, 11.lyr - 999999.lyr

I want to be able to write a query that shows me all 4 digit combinations: 0000.lyr-9999.lyr. Is there anything I can do without needing to use an in-statement?

mysql sql
1 Answers

Simply convert your value to numeric one and check.

SELECT *
FROM tablename
WHERE 0 + columnname BETWEEN 0 AND 9999;

If 0.lyr and 0000.lyr are different values and you need only the row with 2nd value then use rregular expression check.

SELECT *
FROM tablename
WHERE columnname REGEXP '^\\d{4}.lyr$';

fiddle

Akina0
Related