locked
Alphanumeric Between Clause RRS feed

  • Question

  • Experts - 

    I need to use a range for some alphanumeric values in a WHERE clause, e.g. F1010 through F1999.  Other posts have indicated BETWEEN will work here but I am getting the message: 

    "Conversion failed when converting the varchar value 'F1010' to data type int."

    What am I missing?

    where field between 'F1010' and 'F1999'

    Thank you!


    Bonediggler

    Thursday, February 7, 2019 5:30 PM

Answers

  • Can you show your entire SELECT? Is field defined as an integer? Is field the correct column name that contains the 'F1010' data? In your select are you referencing field or another column that contains 'F1010' data?

    This shows that provided the column name is defined as varchar, there is no problem using the between with varchar data. 

    If object_ID(N'tempdb..#temp') is not NULL
    	drop table #temp
    
    create table #temp (c1 varchar(10))
    
    insert into #temp values ('f10'),
    ('f12'), ('f13'), ('f14'), ('f1'), ('f111'),
    ('f22'), ('F2'), ('g11'), ('e'),('f')
    
    select * from #temp where c1 between 'f10' and 'f12'
     

    • Marked as answer by Bonediggler Thursday, February 7, 2019 5:44 PM
    Thursday, February 7, 2019 5:38 PM

All replies

  • Can you show your entire SELECT? Is field defined as an integer? Is field the correct column name that contains the 'F1010' data? In your select are you referencing field or another column that contains 'F1010' data?

    This shows that provided the column name is defined as varchar, there is no problem using the between with varchar data. 

    If object_ID(N'tempdb..#temp') is not NULL
    	drop table #temp
    
    create table #temp (c1 varchar(10))
    
    insert into #temp values ('f10'),
    ('f12'), ('f13'), ('f14'), ('f1'), ('f111'),
    ('f22'), ('F2'), ('g11'), ('e'),('f')
    
    select * from #temp where c1 between 'f10' and 'f12'
     

    • Marked as answer by Bonediggler Thursday, February 7, 2019 5:44 PM
    Thursday, February 7, 2019 5:38 PM
  • Thanks - the field I thought was varchar is actually int.  Back to the drawing board....

    Bonediggler

    Thursday, February 7, 2019 5:44 PM