Best way to filter data for a given range-CodePudding

I need to find Claims from a given table having a procedure code between the range 'Q5000' and 'Q5090'. I am able to write a function for Int codes but am not sure how to best deal with characters range in SQL Server?

I can manually write all the codes and filter them but is there any way by which I provide the first and Last Value and SQL generates the full set of values.

Select * 
from   dbo.claims 
where  ProcedureCode in ('Q5000',Q5001','Q5002',....,'Q5090')

CodePudding user response：

You could try this simple hack

SELECT * FROM dbo.claims WHERE ProcedureCode LIKE 'Q50__' AND TRY_CONVERT(INT, RIGHT(ProcedureCode, 2)) < 91;

CodePudding user response：

This answer in another thread may be helpful for you.

You could also try this assuming same single char is the beginning char.

SELECT * FROM dbo.claims WHERE RIGHT(ProcedureCode, LEN(ProcedureCode)-1) BETWEEN 5000 AND 5090

CodePudding user response：

If the strings have the same length, and start with the same letters?
Then you can simply use BETWEEN.

Select * 
from   dbo.claims 
where  ProcedureCode BETWEEN 'Q5000' AND 'Q5090'

You see, the problem with character compare is when the length's differ.

For example, the string '2' is bigger than the string '10'.
Because the character '2' comes after '1'.
But 'A02' is smaller than 'A10'.