SQL query to retrieve range of alphanumeric data

[edpfister]

So I've looked for an answer to this question on the web but couldn't find anything that covers this specifically.

Our database stores the project version in the format #.#.# (Rev_#) so there is both text and numbers in the string. I have no control over the data so I am stuck with this format. Our users have the need to retrieve a range of these projects and the specific problem has to do with the rev portion. If they want to retrieve something like 10.1.100 (Rev_2) to 10.1.100 (Rev_11) they will get no results as 1 is less than 2 when treated as a string. Is there a way to write a general SQL statement that will handle this condition? I would prefer to not refer to "Rev" in the statement so that I can keep this general if possible. If that's not possible I could do that though as a last resort.

Any help would be appreciated. I know the basics of SQL queries but can't write anything too complicated.

[BitBlt]

One possibility is to add columns to your source table, then parse and store the node numbers separately. Then it becomes easy to query. However, that isn't always possible so here is an alternative.

Writing this one was fun and horrifying at the same time. The following set of statements work and the scenario seems to meet your requirements, but hold your nose:

-- First, create an interim table
create table #temp1
(
id int,
RevNumber varchar(25),
Node1 int,
Node2 int,
Node3 int,
Node4 int
)
-- Now insert into it
insert into #temp1
(id, RevNumber, Node1, Node2, Node3, Node4)
SELECT id
      , RevNumber
      , LEFT(RevNumber, CHARINDEX('.', RevNumber, 1) - 1) as Node1
      , SUBSTRING(RevNumber, 
			CHARINDEX('.', RevNumber, 1) + 1, 
			(CHARINDEX('.', RevNumber, CHARINDEX('.', RevNumber, 1) + 1) - CHARINDEX('.', RevNumber, 1)) - 1
			)
			 as Node2
      , SUBSTRING(RevNumber, 
			(CHARINDEX('.', RevNumber, CHARINDEX('.', RevNumber, 1) + 1) + 1), 
			CHARINDEX('(', RevNumber, 1) - CHARINDEX('.', RevNumber, CHARINDEX('.', RevNumber, 1) + 1) - 1
			) 
			as Node3
      , SUBSTRING(RevNumber, 
			CHARINDEX('_', RevNumber, 1) + 1, 
			LEN(RevNumber) - CHARINDEX('_', RevNumber, 1) - 1
			) 
			as Node4
  FROM [dbo].[tblRevNumberTest]
-- Now select from the interim table, with some sample selection criteria
select * from #temp1
where CAST(Node1 as varchar(3)) + '.' + CAST(Node2 as varchar(3)) + '.' + CAST(Node3 as varchar(3)) = '1.1.2'
and Node4 between 6 and 12
order by Node1, Node2, Node3, Node4
-- Be sure to clean up after yourself!
drop table #temp1

Basically the heavy lifting is being done using a combination of "CHARINDEX" to isolate the specific nodes whether in the #.#.# part or the Rev_# part, then put them in a temp table so that querying is a little simpler.

Here's the test table/data I used to test. I tested on SQL2008, but it should work on 2005 and 2000 as well:

create table dbo.tblRevNumberTest
(
id int identity(1,1) not null,
RevNumber varchar(25)
)
-- insert some test data...notice that it's scrambled so the ID numbers won't be in order when sorting later
insert into dbo.tblRevNumberTest
(RevNumber)
values
('1.1.12(Rev_1)'),('1.1.12(Rev_2)'),('1.1.12(Rev_3)'),('1.1.12(Rev_4)'),('1.1.12(Rev_5)'),
('1.1.12(Rev_6)'),('1.1.12(Rev_7)'),('1.1.12(Rev_8)'),('1.1.12(Rev_9)'),('1.1.12(Rev_10)'),
('1.1.12(Rev_11)'),('1.1.12(Rev_12)'),('1.1.12(Rev_13)'),('1.1.12(Rev_14)'),('1.1.12(Rev_15)'),
('1.1.12(Rev_16)'),('1.1.12(Rev_17)'),('1.1.12(Rev_18)'),('1.1.12(Rev_19)'),('1.1.12(Rev_20)'),
('1.1.12(Rev_31)'),('1.1.12(Rev_110)'),('1.1.2(Rev_1)'),('1.1.2(Rev_2)'),('1.1.2(Rev_3)'),
('1.1.2(Rev_4)'),('1.1.2(Rev_5)'),('1.1.2(Rev_6)'),('1.1.2(Rev_7)'),('1.1.2(Rev_8)'),('1.1.2(Rev_9)'),
('1.1.2(Rev_10)'),('1.1.2(Rev_11)'),('1.1.2(Rev_12)'),('1.1.2(Rev_13)'),('1.1.2(Rev_14)'),
('1.1.2(Rev_15)'),('1.1.2(Rev_16)'),('1.1.2(Rev_17)'),('1.1.2(Rev_18)'),('1.1.2(Rev_19)'),
('1.1.2(Rev_20)'),('1.1.2(Rev_31)'),('1.1.2(Rev_110)'),('1.1.1(Rev_1)'),('1.1.1(Rev_2)'),
('1.1.1(Rev_3)'),('1.1.1(Rev_4)'),('1.1.1(Rev_5)'),('1.1.1(Rev_6)'),('1.1.1(Rev_7)'),
('1.1.1(Rev_8)'),('1.1.1(Rev_9)'),('1.1.1(Rev_10)'),('1.1.1(Rev_11)'),('1.1.1(Rev_12)'),
('1.1.1(Rev_13)'),('1.1.1(Rev_14)'),('1.1.1(Rev_15)'),('1.1.1(Rev_16)'),('1.1.1(Rev_17)'),
('1.1.1(Rev_18)'),('1.1.1(Rev_19)'),('1.1.1(Rev_20)'),('1.1.1(Rev_31)'),('1.1.1(Rev_110)')

Of course, you'll have to tweak this to fit your specific scenario, but the technique should be the same. And, it assumes that all the RevNumbers have the same format. If even one of them doesn't, this will fail.

Last, you could also include the actual ID of the source row to include with your results. That way you could join back to the source table on that ID and get all the details without having to drag them along into your temp table.

Best of luck! Hope this all helps!

[adam_k]

Great solution, as always.
You could make it a bit better by skipping the temp table and sticking the whole insert into a view. An indexed one for best performance, provided schemabinding is OK.

[BitBlt]

Good point, I agree...and it would hide some of the ugliness. :icon_cheesygrin: