SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hi, BETWEEN for VARCHAR type field.

    Hi,

    My employer asked me to debug a part of the app. and it is written by a co-worker.
    In the buggy query I found this in the table :
    Code MySQL:
    notice_period   Type:	varchar(255)
    and in the query:
    Code MySQL:
    `notice_period` BETWEEN 0 AND 30

    This conditions :
    "BETWEEN 0 AND 30" return the results thar are >0 and <30, and other results that have in the field chars (like "No payment"). I can't change the table structure because the app. is very big. I need a work around.

    Do you know haw I can make this query to work ?

    If I search "BETWEEN 0 AND 30" it will give me only the results that have the 'notice_period' BETWEEN 0 AND 30 and not the rows that have other text in it ?

    Thank you for the time,
    Pistocel.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you neglected to mention which database you're using

    this is the Databases forum and there is a separate subform for MySQL, but i'm going to guess you're using MySQL anyway, because of the wacky backticks

    try this --
    Code:
    WHERE notice_period REGEXP '[^[:digit:]]' = 0
      AND notice_period BETWEEN 0 AND 30
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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