How can i get a range of number of characters in SQL Server using wildcard characters

I have a query where i need to filter the value whether it has 6 or 7 or 8 or 9 characters not more than that. I know this

SELECT * FROM TABLE_NAME WHERE col LIKE '______' OR col LIKE '_______' and so on..


Is there a general way to filter the number of characters. what if it varies a long range? Do i need to put this OR for that much conditions

select * from table where len(col) between 6 and 9
– Chanukya
Jun 29 at 8:54

This sounds like an XY Problem. You have a problem X and think that filtering by length (Y) is the solution. When Y doesn't work you ask about Y, not the actual problem X. Why do you need to do that at all? Such a query can't use indexes and be forced to scan all rows in the table. Using wildcards won't allow it to use indexes either.
– Panagiotis Kanavos
Jun 29 at 8:55

You coudl use LEN(Col) between 6 and 9 but that won't use indexes either. Why do you care about the length? Does the field contain different types of data perhaps? That would be a design problem. You should use different fields for different types of data, if not different tables. You could also add a separate field to store the category/type of the data. This would allow you to use indexes and perform the search thousands of times faster
– Panagiotis Kanavos
Jun 29 at 8:55

LEN(Col) between 6 and 9

3 Answers
3

LEN (Transact-SQL)

where len(col) between 6 and 9


Try this

if you want minimum 6 char and max 9 char

SELECT * FROM TABLE_NAME WHERE length(col) between 6 and 9


if you want at most 9 char

SELECT * FROM TABLE_NAME WHERE length(col) <= 9


Use len?

SELECT * FROM TABLE_NAME WHERE len(col) between 6 and 9


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.