【Answer】

I have this data in my table and I want to sort it using the numbers in the data.

Data unsorted:

5 team team team team

team team team 2 team

team team team team 4

team 1 team team team

team team 3 team team

Data Sorted:

team 1 team team team

team team team 2 team

team team 3 team team

team team team team 4

5 team team team team

A solution:

Create this function:

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint

RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

 

CREATE FUNCTION GetNumericOnly (val VARCHAR(255))

RETURNS VARCHAR(255)

BEGIN

DECLARE idx INT DEFAULT 0;

IF ISNULL(val) THEN RETURN NULL; END IF;

 

IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 

SET idx = LENGTH(val);

 WHILE idx > 0 DO

 IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN

 SET val = REPLACE(val,SUBSTRING(val,idx,1),"");

 SET idx = LENGTH(val)+1;

 END IF;

 SET idx = idx - 1;

 END WHILE;

 RETURN val;

END;

Then use it like this:

SELECT * FROM mixedvalues

ORDER BY GetNumericOnly(value)

 

【Answer】

It’s inconvenient to get same type of values from different columns in SQL. Yet it’s easy to handle it in SPL (Structured Process Language). First I convert field values in each record into an array, get numeric values and sort records by them. The code is simple and easy to understand:

	A
1	$select * from tb
2	=A1.sort(~.array().select(ifnumber(~)))

A1: Retrieve data from table tb.

A2: Convert field values in each record into a sequence, get the numeric member from it, and sort A1’s rows by the order of these numbers.

You can call the SPL script from a third application, to learn more details, see How to Call an SPL Script in Java.

Learn more examples of achieving dynamic columns at http://blog.raqsoft.com/?p=5010.