December 18, 2003 at 1:40 pm
Hi
I have the following issue
I have a table with following values
T1
c1 c2 c3
1 0100T 9999T
2 00000 09999
3 10000 69999
When i am trying to find wht range does '55555' lies in the table for column c2 and c3. I am getting two rows 1 and 3.
SQL
select * from T1 where '55555' between c2 and c3.
The result is
1 0100T 9999T
3 10000 69999
I am expecting to get only row 3.
SQL
select * from T1 where '5555T' between c2 and c3.
The result is
1 0100T 9999T
3 10000 69999
I am expecting to get only row 1.
In other words how can i perform bit by bit comparison in a query.
Could someone please help me out in this
Thanks
chinthanai
December 18, 2003 at 1:54 pm
Think you want something like this:
create table t1(c1 int, c2 char(5), c3 char(5))
insert into t1 values(1, '0100T', '9999T')
insert into t1 values(2, '00000', '09999')
insert into t1 values(3, '10000', '69999')
select * from t1
select * from T1 where '5' between substring(c2,1,1) and substring(c3,1,1) and
'5' between substring(c2,2,1) and substring(c3,2,1) and
'5' between substring(c2,3,1) and substring(c3,3,1) and
'5' between substring(c2,4,1) and substring(c3,4,1) and
'5' between substring(c2,5,1) and substring(c3,5,1)
select * from T1 where '5' between substring(c2,1,1) and substring(c3,1,1) and
'5' between substring(c2,2,1) and substring(c3,2,1) and
'5' between substring(c2,3,1) and substring(c3,3,1) and
'5' between substring(c2,4,1) and substring(c3,4,1) and
'T' between substring(c2,5,1) and substring(c3,5,1)
drop table t1
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply