October 28, 2010 at 7:19 am
I need to figure out how to select a record where the criteria falls between values contained within two different columns AND the values might contain an alphanumber prefix (N, SU, SL, SL-, SL[space], etc.). For example, if the user enters 'N2100', the record returned would be the one with a Start of 'N2001' and and End of 'N2500' - ('Zone N-3').
My thought is to convert the leading prefixes of the criteria, start and end to their ASCII equivalents and then convert the result to an integer. N2100 would become 782100 with the start/end becoming 782001 and 782500.
[Code]
CREATE TABLE [dbo].[_test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RangeStart] [varchar](8) NOT NULL,
[RangeEnd] [varchar](8) NOT NULL,
[Description] [varchar](50) NOT NULL)
GO
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('100','1200','Zone 1')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('1201','2000','Zone 2')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('2001','2500','Zone 3')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('N100','N1200','Zone N-1')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('N1201','N2000','Zone N-2')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('N2001','N2500','Zone N-3')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('C100','C1200','Zone C-1')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('C1201','C2000','Zone C-2')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('C2001','C2500','Zone C-3')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('SU100','SU1200','Zone SU-1')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('SU1201','SU2000','Zone SU-2')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('SU2001','SU2500','Zone SU-3')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('SL100','SL1200','Zone SL-1')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('SL1201','SL2000','Zone SL-2')
INSERT INTO _test(RangeStart, RangeEnd, Description) Values ('SL2001','SL2500','Zone SU-3')
GO
[/code]
October 28, 2010 at 7:47 am
The bigger problem is that your field is a composite field. A better solution would be to break it down into the identifying parts:
Prefix varchar(5), PrefixID int
To help you with your issue, you might want to take a look at the PATINDEX function. This might help you out:
SELECT StartPrefix = LEFT(RangeStart, PatIndex('%[0-9]%', RangeStart)-1),
StartID = CONVERT(int, SubString(RangeStart, PatIndex('%[0-9]%', RangeStart), 8)),
EndPrefix = LEFT(RangeEnd, PatIndex('%[0-9]%', RangeEnd)-1),
EndID = CONVERT(int, SubString(RangeEnd, PatIndex('%[0-9]%', RangeEnd), 8))
FROM _test
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 7:58 am
And then break out the prefix from the criteria and apply the derived criteria against the derived columns?
October 28, 2010 at 8:08 am
I wouldn't trust the ASCII-value solution. Too subject to things like collation values or the introduction of a two-character prefix or whatever.
Definitely split the values.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 8:09 am
david.holley (10/28/2010)
And then break out the prefix from the criteria and apply the derived criteria against the derived columns?
Precisely. Something like this:
declare @SearchRange varchar(8);
set @SearchRange = 'N2100';
declare @SearchPrefix varchar(8),
@SearchID int;
SELECT @SearchPrefix = LEFT(@SearchRange, PatIndex('%[0-9]%', @SearchRange)-1),
@SearchID = CONVERT(int, SubString(@SearchRange, PatIndex('%[0-9]%', @SearchRange), 8));
WITH CTE AS
(
SELECT StartPrefix = LEFT(RangeStart, PatIndex('%[0-9]%', RangeStart)-1),
StartID = CONVERT(int, SubString(RangeStart, PatIndex('%[0-9]%', RangeStart), 8)),
EndPrefix = LEFT(RangeEnd, PatIndex('%[0-9]%', RangeEnd)-1),
EndID = CONVERT(int, SubString(RangeEnd, PatIndex('%[0-9]%', RangeEnd), 8)),
Id,
RangeStart,
RangeEnd,
Description
FROM _test
)
SELECT Id,
RangeStart,
RangeEnd,
Description
FROM CTE
WHERE StartPrefix = @SearchPrefix
AND @SearchID BETWEEN StartID AND EndID;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 10:35 am
Thanks a bunch that goes a long, long way to actually doing what I need to do.
October 28, 2010 at 11:22 am
david.holley (10/28/2010)
Thanks a bunch that goes a long, long way to actually doing what I need to do.
If it's possible to do, what really needs to be done is to separate the prefix from the id and make separate columns out of it. Otherwise, you're going to end up with a lot of code doing this weird stuff.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 11:49 am
So go with two functions - one that extracts the alpha-prefix and the numeric from the value entered from the user and another that combines the two for display. Should be simple enough. Since its for an ASP.NET based application, I'd be inclined to do it all on the db side.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply