SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Rows Not Ending with 4 Digit Year

    I have a table of rows that have a "release_date" column I need to clean up. It's a varchar column that has data like:

    "2012"
    "January 10, 2013"

    "January 5, 13"
    "December 10"
    "November 6, 2011 (US)"

    Now, the top two samples are okay, but the bottom three are not. I'm trying to figure out a query that will select all rows that don't have the last four characters of the "release_date" column not matching a properly formatted 4-digit year. Trying to think of a query that can do a 4-character substring of each row to confirm the 4 numeric characters at the end of the string.

    All feedback appreciated.

    Cheers!
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SitePoint Member
    Join Date
    Dec 2013
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM your_table_name WHERE `release_date` REGEXP '[0-9]{4}$';

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by blue_sky View Post
    SELECT * FROM your_table_name WHERE `release_date` REGEXP '[0-9]{4}$';
    shouldn't there be a NOT in there somewhere? OP wanted rows which ~don't~ end in 4 digits
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You guys are awesome! And yes, had to add the "not"

    SELECT * FROM your_table_name WHERE `release_date` NOT REGEXP '[0-9]{4}$';

    Cheers!
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


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
  •