Can we use "Between" operator for a VARCHAR column?

• sathish802004

  SSC Veteran

  Points: 278

  More actions

  October 6, 2017 at 1:40 pm

  #340590

  I have a table with serial # (which is a varchar column). If i have to find a range from this table can i use between operator?

  Table will have the below structure:

  1. Part # Varchar(40)
  2. Serial # Varchar(40)
  3. Quantity int
  ... 

  how to find the range of serials between "004-ABC" and "78B-343" ?

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  October 6, 2017 at 1:53 pm

  #1962701

  sathish802004 - Friday, October 6, 2017 1:40 PM

  I have a table with serial # (which is a varchar column). If i have to find a range from this table can i use between operator?

  Table will have the below structure:

  1. Part # Varchar(40)
  2. Serial # Varchar(40)
  3. Quantity int
  ... 

  how to find the range of serials between "004-ABC" and "78B-343" ?

  Sure you can, though whether it returns the results you are hoping for is another matter.
  DECLARE @x TABLE (SomeText VARCHAR(50));

  INSERT @x
(
  SomeText
)
VALUES
('004-ABC')
,('78B-343')
,('1')
,('Z')
,('3')
,('9');

  SELECT *
FROM @x x
WHERE
   x.SomeText BETWEEN '004-ABC' AND '78B-343';

  ---

• whenriksen

  SSCarpal Tunnel

  Points: 4752

  More actions

  October 9, 2017 at 8:38 am

  #1962838

  BETWEEN on a character column works different than it would with numeric or date/time columns

  '0'
  '1'
  '111111111'
  '2'
  '2222222'

  WHERE col BETWEEN '1' AND '2'
  would return:
  '1'
  '111111111'
  '2'

  Character sorting works on individual characters from left to right (depending on language settings), just like a dictionary sorts words.  The word length doesn't matter in the sort, only the character in the column being sorted.

  Wes
  (A solid design is always preferable to a creative workaround)