Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    New Jersey
    Posts
    1

    Question Unanswered: SQL Where Clause Question

    In my MS 2000 db I've got a varchar column called 'width'.
    It has values like 3/4" or 5/8", etc.

    I'd like to write a where clause in a SQL query that does
    something like this:

    WHERE 0=0
    AND (WIDTH BETWEEN #FORM.Width1# AND #FORM.Width2#)
    [i.e. -- (AND (0.75 BETWEEN 0.5 AND 1))]

    What do I need to do to make this work? Covert? Cast?
    I have know idea...please help!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: SQL Where Clause Question

    Originally posted by Jerky19
    In my MS 2000 db I've got a varchar column called 'width'.
    It has values like 3/4" or 5/8", etc.

    I'd like to write a where clause in a SQL query that does
    something like this:

    WHERE 0=0
    AND (WIDTH BETWEEN #FORM.Width1# AND #FORM.Width2#)
    [i.e. -- (AND (0.75 BETWEEN 0.5 AND 1))]

    What do I need to do to make this work? Covert? Cast?
    I have know idea...please help!!!

    What is #FORM.Width1# ? A variable?

    I'm also not sure whether Width is a reserved word. In that case you would need to write [Width] Between x AND y

    Finally, I'm also not sure whether a decimal data type may be used in a BETWEEN statement
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    DoktorBlue, width is NOT a reserved word and you can use a decimal datatype in a between function.

    Jerky19, what a nightmare! Not sure what your code looks like but if I was forced to keep width as a varchar I would write a function to return the "decimal" equivilant of width.

    for example:
    Code:
    declare @Val varchar(15)
    set @Val = '5/8"'
    select @Val as 'Original Value'
         , cast(substring(@Val,1,charindex('/',@Val) -1) as float) /
           cast(substring(replace(@Val,'"',''),charindex('/',@Val) +1, 15) as float) as '''Converted Value'''
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Paul Young's function is the best general solution. If you are working with a defined set of fractions (such as I did in a recipe database once), then you could also use a lookup table that stored the fraction in both string and decimal form. This would have the added bonus of being a good rowsource for list boxes and combo boxes, and for enforcing that only valid fractions are entered (imagine some user entering "2 1/2", "5/10/2", or even "3\2").

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •