SQL Query Between Two Strings

Question

I need Records between two strings passing through Parameters. @startString and @end String.
Between Query is possible between in these two strings?

10-00-000-000000 and 12-02-023-000000

Sure, you can do it but whether the results are what you expect is a different matter. What have you tried already? What, exactly, are you stuck on? — Rich Benner, Jun 27 '16 at 07:31
Is your start and end string is datetime or date or something else — StackUser, Jun 27 '16 at 07:56

score 0 · Accepted Answer · answered Jun 27 '16 at 07:39

0

Replace your column with Column1

SELECT * FROM TABLE1 WHERE CONVERT(BIGINT,REPLACE(@Column1,'-','')) BETWEEN 
CONVERT(BIGINT,REPLACE(@startString,'-','')) AND 
CONVERT(BIGINT,REPLACE(@endString,'-',''))

answered Jun 27 '16 at 07:39

Keppy

471
1
7
23

@RanaAli This approach is very erronous (if you are not absolutely sure that the format is the same). With many rows it will be very slow due to the heavy string and cast operations. And read about [sargable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). You should avoid to do computations on the columns you are ussing in `where` or `join` or other places, where indexes might to an important job... – Shnugo Jun 27 '16 at 08:44

score 0 · Answer 2 · answered Jun 27 '16 at 08:24

If your strings 10-00-000-000000 and 12-02-023-000000 are always in the same format xx-xx-xxx-xxxxxx (2-2-3-6 numbers) you can compare them on alphanumeric base just using

WHERE YourColumn BETWEEN @startString AND @endString

I'm assuming, that YourColumn is a string column with exactly the same format.

But if the numbering/format might differ you first have to specify What is the implicit sort order? Is 10-90-... higher or lower than 10-100-... ?

SQL Query Between Two Strings

2 Answers2