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

blue_sky2
—
2013-12-26T01:52:22Z —
#2

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

r937
—
2013-12-26T18:29:50Z —
#3

blue_sky said:

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

casbboy
—
2013-12-26T22:26:54Z —
#4

You guys are awesome! And yes, had to add the "not"

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