MySql select digits only and order them from varchar column

Question

I have a table with a column called year varchar(100). This column can contain data like

 Year ------- 1901 0 circa 12580 125d4 great year 1012 3411 standard 12 saf 1234

Now I want to order by Year ascending or descending by finding the first 4 digits in the Year field. I want to:

Get only those columns those have 4 or more digits (either alone or with some string)
Extract first 4 digits (if more than 4 or with some integer) or get 4 digits (if column has only 4 digit value)
Order by result set based on above two conditions

I want my result set in ascending order like:

Year 1012 1234 1258 1901 3411

And vise versa in descending order.

I have tried following:

 SELECT * FROM table WHERE YEAR REGEXP '^[0-9]+$' AND LENGTH(YEAR) = 4 ORDER BY CAST(YEAR AS UNSIGNED) DESC;

But this only returns columns having only 4 digits and orders by them, but not the result as I mentioned above.

The best practice would be to create a new column with int year, and sort by that. You may succeed on building a query to sort by an extracted regex but it will not use an index (it's important if you have many rows). If your query it's not for the production app you could create a temporary table, populate it with all the rows in the old table and normalized year value and then do a normal sort on it. — Dan Ionescu
– Dan Ionescu, Commented Feb 28, 2017 at 12:17
@DanIonescu I am currently on production and I cannot create column right now — Awais Qarni
– Awais Qarni, Commented Feb 28, 2017 at 12:19
ok, so the query is for the application, or it's just a one time thing for analising data once ? — Dan Ionescu
– Dan Ionescu, Commented Feb 28, 2017 at 12:20

Awais Qarni · Accepted Answer · 2017-02-28 14:13:59Z

This is how I have solved the problem. I have created a stored function taking help from here (reply from Ushastry) that looks like

CREATE FUNCTION `get_numeric`(`year` VARCHAR(50)) RETURNS INT(11) NO SQL BEGIN DECLARE ctrNumber VARCHAR(50); DECLARE finNumber VARCHAR(50) DEFAULT ' '; DECLARE sChar VARCHAR(2); DECLARE inti INTEGER DEFAULT 1; IF LENGTH(year) > 0 THEN WHILE(inti <= LENGTH(year)) DO SET sChar= SUBSTRING(year,inti,1); SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9'); IF ctrNumber > 0 THEN SET finNumber=CONCAT(finNumber,sChar); ELSE SET finNumber=CONCAT(finNumber,' '); END IF; SET inti=inti+1; END WHILE; RETURN finNumber; ELSE RETURN 'Invalid'; END IF; END

And then following query works as expected

 SELECT id, YEAR, SUBSTRING(`get_numeric`(YEAR),1,4) AS act_year FROM table HAVING LENGTH(act_year) = 4 ORDER BY act_year ASC;

Collectives™ on Stack Overflow

MySql select digits only and order them from varchar column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related