Is there a Better way to apply range for String Data type?

Question

Post reply

Is there a Better way to apply range for String Data type?

Mohammed Yousuf

Say Hey Kid

Points: 696
More actions
July 18, 2012 at 1:28 am

#276065

I want to apply a range ( @fromvalue to @tovalue) to a column of type varchar.
lets say the column is ItemNumber
The ItemNumber can have both numeric and non-numeric data like '1','010','01-123','09/452','H01','F22/1', etc.
In most case we prefix both column and query parameters to a fixed length (say 10digits) and apply the condition.
The condition looks like
WHERE RIGHT('0000000000'+ItemNumber , 10) BETWEEN RIGHT('0000000000'+@fromvalue , 10) AND RIGHT('0000000000'+@tovalue , 10)
My question is
Is there a better way to do this?
Does this method has an impact on performance?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Not sure why are you using all this for range --> use substring instead

Check this link:

http://msdn.microsoft.com/en-us/library/ms187748.aspx

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

Another link for you with good examples:

http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=4ab06421-e859-4b5f-a948-0c9640f3108d&tkw=uses-of-the-substring-string-function

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

lokeshvij (7/18/2012)

Not sure why are you using all this for range --> use substring instead

Check this link:

http://msdn.microsoft.com/en-us/library/ms187748.aspx%5B/quote%5D

I am not sure how substring would help in this case but I would be interested to see an example solution using substring that would solve the OP's issue.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Mohammed Yousuf (7/18/2012)
I want to apply a range ( @fromvalue to @tovalue) to a column of type varchar.
lets say the column is ItemNumber
The ItemNumber can have both numeric and non-numeric data like '1','010','01-123','09/452','H01','F22/1', etc.
In most case we prefix both column and query parameters to a fixed length (say 10digits) and apply the condition.
The condition looks like
WHERE RIGHT('0000000000'+ItemNumber , 10) BETWEEN RIGHT('0000000000'+@fromvalue , 10) AND RIGHT('0000000000'+@tovalue , 10)
My question is
Is there a better way to do this?
Does this method has an impact on performance?

I see you are pretty new around here. It is considered best practice to include ddl, sample data and desired results. You can read the first link in my signature for more details about best practices when posting questions.

I took the liberty of creating something that as closely as possible resembles your question as I understand it.

declare @From varchar(10), @To varchar(10)

set @From = '?'

set @To = '?'

;with Items (ItemNumber)

as

(

select '1' union all

select '010' union all

select '01-123' union all

select '09/452' union all

select 'H01' union all

select 'F22/1'

)

select * from Items

--WHERE ???

What I don't understand is what you are looking as output. Using the above as a starting point what would be the values for @From and @To and what would be the desired result?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/