SQL 2005: Selecting Values within a Range 1

[tahirk]

Hi hope someone can give me some pointers on an issue I have been having with a stored procedure requirement.

I have developed a custom user control (C#/ASP.NET 3.5) which calls a stored procedure on my departments development SQL 2005 Server.

The stored procedure is proving a little difficult to workout as I am not entirely sure how I should code it up. Here is what I want to do:

1. Pass in two string values e.g "A" and "C"
2. Using those two input values, select from a table/view values that fall within the range of the two input values i.e

select values from a table/view where column 1 has values starting with "A", select values starting with "C" and select everything in between i.e anything beginning with "B" (case insensitive).

I came up with something like this but kind of stuck on how I should go about doing a range search where I am also doing a like search on some string value:

SELECT * Column1 FROM myTable1 WHERE
	 Column1 like '' + @rangestart + '%' OR Column1 like '' + @rangeend + '%'

The above works fine if I only want two sets of values i.e anything beginning with "A" and anything beginning with "B".

What I would like to do is to be able to set a ranged search e.g from "A" to "Z", search within this range for anything beginning with "A", "B", "C" etc

Cheers,

Tahir

 

[tahirk]

Lol, I worked out the answer after typing out this post!

Here is my solution (simplest one's as always):

select somevalue from mytable where myvalue in 
	(select distinct myvalue from mytable where myvalue >= ('' + @rangestart + '%')
		and myvalue <= ('' + @rangeend + '%')) -- selecting distinct so I don't get duplicate values returned

The above works fine for me, anyone have a better/more elegant solution?

Cheers,

T

 

[ca8msm]

You could use a regular expression to find the rows that start in a particular range. Have a quick search on google to see how regular expressions are implemented in sql server.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[tahirk]

*oops: somevalue = myvalue, above code was hacked together quickly as an example*

 

[tahirk]

Cheers mate, yeah I was thinking about regex but wasn't sure about using in SQL, I should have taken a look as I am sure it will be far more flexible and elegant for my future needs.

T

 

[tahirk]

Found a good resource:

http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx

Looks like exactly what I need.

 

[tahirk]

Had a good read up on RegEx for SQL Server and ended up implementing some custom CLR code on our SQL2k5 box that proved useful for other projects.

For my requirement I found this to be best (and simplest) solution:

SELECT DISTINCT column1 FROM mytable 
	WHERE column LIKE '[' + @startrange + '-' + @endrange + ']%'

The like wildecard [#-#]% works for me i.e I get the results from a varchar column within the specified range.