Bit Comparison

• chinthanai

  Grasshopper

  Points: 11

  More actions

  December 18, 2003 at 1:40 pm

  #81804

  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

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  December 18, 2003 at 1:54 pm

  #487071

  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