July 13, 2009 at 12:55 pm
I am having a problem with the between statement returning rows I don't want from a query. I am sure that someone has seen or had to work around this before so I am hoping for some help.
Rather than elaborate too much I will provide a simple setup and ask for a solution.
Here is my table setup:
--begin table create
CREATE TABLE [dbo].[find_between](
[FromCode] [varchar](7) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ThruCode] [varchar](7) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[Description] [varchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
) ON [PRIMARY]
--I insert data into the table
insert dbo.find_between
values ('0001F','6020F','RANGE A')
insert dbo.find_between
values ('28298','28298','RANGE B')
-- next I want to find all rows where a value is between the from and thru code
Declare @code varchar(7)
Select @code = '28298'
select * from find_between
where @code between FromCode and ThruCode
My results always returns both rows but in actuality I only want the second row 'RANGE B'. Is there a simple was to do this withOUT looking at the substring to see if it is all numeric or numeric and alpha?
always get a backup before you try that.
July 13, 2009 at 1:26 pm
You can use pattern search with like. Here is an example that shows you how to search only for strings that have only numeric digits. You can read about pattern search in BOL (Books On Line, the help file that is installed when you install SQL Server) for a good explanation about it:
select * from find_between
where @code between FromCode and ThruCode
and FromCode not like '%[^0-9]%' AND ThruCode not like '%[^0-9]%'
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2009 at 1:32 pm
What are those values? Are they hex or some such?
Varchar does alphabetical comparisons for Between. Thus, since "2" is between "0" and "6", you're going to get that row.
You could use IsNumeric to test for mixes of numbers and letters. Don't have to use substrings for that. Would look like this:
select * from find_between
where @code between FromCode and ThruCode
and IsNumeric(@code) = IsNumeric(FromCode)
and IsNumeric(@code) = IsNumeric(ThruCode);
With that, if @code is a number, it will only find rows where From and Thru are also numbers, and if it isn't a number, it will only find rows where they also aren't.
Keep in mind that "1E6" is a number per IsNumeric. You'll want to check out IsNumeric in Books Online or on MSDN to get all the limitations and such on it.
- 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
July 14, 2009 at 4:13 am
There are many better solutions, but in the interests of answering the question directly, the following 'works':
CREATE TABLE [dbo].[find_between](
[FromCode] sql_variant NOT NULL,
[ThruCode] sql_variant NOT NULL,
[Description] [varchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
) ON [PRIMARY]
insert dbo.find_between
values ('0001F','6020F','RANGE A')
insert dbo.find_between
values (28298,28298,'RANGE B')
Declare @code int
Select @code = 28298
select * from find_between
where @code between FromCode and ThruCode
Declare @code2 varchar(7)
Select @code2 = '28298' COLLATE SQL_Latin1_General_CP1_CS_AS
select * from find_between
where @code2 between FromCode and ThruCode
July 14, 2009 at 6:38 am
Thanks to everyone. I need to read and digest what you wrote and I will let you know what approach I take.
To answer the question the codes are not hex. They CPT codes. They are the codes that physicians use to bill insurers. The codes with the alpha codes in them are temporary codes until they can be assigned a numeric code.
Here is an example of some of the codes:
81000 = Urinalysis
66984 = Cataract Eye surgery
always get a backup before you try that.
July 14, 2009 at 11:23 am
I completely appreciate all the responses. SSCertifiables approach will resolve my issue completely. The codes I could range from are always in three possible formats
4 numerals and one alpha
one alpha and 4 numerals
5 numerals
so the IsNumeric worked for every possible combination I could throw at it. I'm kind of a poor SQL programmer and you guys made me look like a genius as our senior DBA had been fighting this for some time.
WOOHOO!
always get a backup before you try that.
July 15, 2009 at 8:03 am
FYI for those reading, SSCertifiable = Gsquared.
Gus, thanks for this, I'd always just split the search into two parts, one for true CPT, and one for CPT level II/III and HCPCS (read: the ones with alpha characters). This is much easier.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply