Between + '%'

Between + '%'

• createdbyx

  Read up dude, I think we have this one. Teamwork
  
  
  

• mtower

  Thank you, Barry. I choked on my original response!
  

  Dave

  
  

• Darren McGowan

  First, you don't need to concatenate the percent sign when using it as a wildcard... With that said, you can't use a wildcard in a between operation...
  
  Try casting your Code column to an integer and using between to compare them as numbers. That is if they are all 8 characters in length, this will work nicely.
  
  .... between 60200000 and 79999999
  
  Phil
  
  
  

• muundahweed

  Barry Andrew wrote:

  BilalShouman wrote: thanks for this reply but as i told codes staring with 7 are not included Ah, have you tried to see what happens once you switch the 7 for an eight

  
  
  Stop it!!! :)
  
  This is incorrect advice. Using your method is treating the percent sign as an ascii character, not a wildcard. You can only use the wildcard character (%) in a LIKE statement, which is designed to do pattern matching.
  
  The ascii value for '%' is 37, while the number zero (0) is 48. That's why your code "works" on the left side, but not the right side, and why changing that to an 8 works as well.
  
  
  

• JUANCARLOSR

  Dave:
  
  what does char(255) stand for, and can i use this query in
  dim row() as datarow=dt.select("code >= '601' and code <= '7' + Char(255)")
  

• DiZASTiX

  hi,
  
  SELECT
  ID, Code
  FROM
  Table1
  WHERE
  Code => '1%' AND Code =< '7%'
  
  hth
  
  Barry Andrew
  
  
  

• AshKnet

  thanks for this reply
  
  but as i told codes staring with 7 are not included
  

• ar_pad

  the user might not know the total code so he might need
  codes from 6 till 7, or from 602 till 7, or from 1 till 701
  any ideas
  

• UncleSam89

  Where are u guys

  any suggestions

  
  

• aeonblaire

  BilalShouman wrote:

  thanks for this reply but as i told codes staring with 7 are not included

  
  
  Ah, have you tried to see what happens once you switch the 7 for an eight
  
  
  

• Shu Gao

  Ah you beat me to it!
  
  Oh well, using Mugambo's spliffy code here is an SP Where you could enter changing values.
  
  CREATE PROCEDURE stp_GetBetweens
  @numLow int,
  @numHi int
  AS
  SELECT
      Id, Code
  FROM
      MyTable
  WHERE
      Code Between @numLow AND @numHi + char(255)
  GO
  
  So in your app, pass it the two values you want it to use and it does the rest.
  
  hth
  
  Barry Andrew
  
  
  

• OmegaMan

  ugh I see what you mean. lol my apologies sir, ill just chop that SP I wrote for him and lets see if we can nail it.
  
  
  

• Sven Luedemann

  BilalShouman wrote:

  the user might not know the total code so he might need codes from 6 till 7, or from 602 till 7, or from 1 till 701 any ideas

  
  
  EDIT, due to above post, SP is wrong, sorry, im thinking of another right now = )
  Barry
  
  
  

• lucerias

  Sorry, Bilal, I deleted my post to hastily.  The char (255) is the maximum possible value of a 8-bit character.  Therefore, I chose to append this to your high boundary.  Is this what you are looking for:

  set nocount on

  declare @mock table
     (    [id]    integer,
          code    varchar (10)
     )
  insert into @mock values ( 1, '10101001')
  insert into @mock values ( 2, '10101002')
  insert into @mock values ( 3, '10102001')
  insert into @mock values ( 4, '10102002')
  insert into @Mock values ( 5, '60101001')
  insert into @Mock values ( 6, '60101002')
  insert into @mock values ( 7, '60102001')
  insert into @mock values ( 8, '60102002')
  insert into @mock values ( 9, '60201001')
  insert into @Mock values (10, '60201002')
  insert into @Mock values (11, '70101001')
  insert into @Mock values (12, '70101002')
  insert into @mock values (13, '70201001')
  --select * from @mock

  select * from @mock where code between '1' and '7' + char(255)

  -- ------------  Output:  ------------

  --   id          code      
  --   ----------- ----------
  --   1           10101001
  --   2           10101002
  --    ...
  --   11          70101001
  --   12          70101002
  --   13          70201001

  select * from @mock where code between '6' and '7' + char(255)

  
  --   id          code      
  --   ----------- ----------
  --   5           60101001
  --   6           60101002
  --   7           60102001
  --   8           60102002
  --   9           60201001
  --   10          60201002
  --   11          70101001
  --   12          70101002
  --   13          70201001

  
  select * from @mock where code between '6' and '701' + char(255)

  
  --   id          code      
  --   ----------- ----------
  --   5           60101001
  --   6           60101002
  --   7           60102001
  --   8           60102002
  --   9           60201001
  --   10          60201002
  --   11          70101001
  --   12          70101002

   

  
  
• Between + '%'