MySQL - creating a custom function for custom sorting

Question

I work with a large set of obsolete data (converted from db flat file) where the field is formatted as the last 2 digits of the record year of the record, followed by a 4-digit step ..

for example, the third record created in 1998 will be "980003", and the eleventh record created in 2004 will be "040011".

I cannot change these values ​​- they exist through their company, are registered with the state, customers, etc. I know that it would be great to divide the year and the rest into separate columns, but this is not possible. I can’t even do it “internally”, since each row has about 300 fields that are all sorted, and they are very used to working with this field as the record identifier.

so I'm trying to execute MySQL UDF (for the first time) to sort. The query is successful, and it allows me to "select the order from the table by custom_sort (whatever)", but the order is not what I expected.

Here is what I use:

DELIMITER // CREATE FUNCTION custom_sort(id VARCHAR(8)) RETURNS INT READS SQL DATA DETERMINISTIC BEGIN DECLARE year VARCHAR(2); DECLARE balance VARCHAR(6); DECLARE stringValue VARCHAR(8); SET year = SUBSTRING(0, 2, id); SET balance = SUBSTRING(2, 6, id); IF(year <= 96) THEN SET stringValue = CONCAT('20', year, balance); ELSE SET stringValue = CONCAT('19', year, balance); END IF; RETURN CAST(stringValue as UNSIGNED); END// 

Entries return only to 96 (thus, arbitrary "if the first 2 characters are less than 96, add" 20 ", otherwise add" 19 "). I am not happy with this bit, but I do not believe where the main problem is.

To throw one more key into the work, it turns out that in 1996 and 1997 they are indicated as 5 digits, following the example described above, but instead of a 4-digit increment, it increases by 3 digits. Again, I suspect this will be a problem, but this is not the main problem.

An example of the returns I receive with this custom_sort:

 001471 051047 080628 040285 110877 020867 090744 001537 051111 080692 040349 110941 020931 090808 001603 051175 

I really have no idea what I'm doing here and have never used MySQL for UDF like this: any help would be appreciated.

Tya

/ Typo EDIT

/ EDIT 2 concat Required value added “year” - getting the same results anyway

Answer 1

You have some problems with your substrings, and casting to int at the end makes it sort the values ​​with more digits at the end, not a year. This should work better;

 DELIMITER // CREATE FUNCTION custom_sort(id VARCHAR(8)) RETURNS VARCHAR(10) READS SQL DATA DETERMINISTIC BEGIN DECLARE year VARCHAR(2); DECLARE balance VARCHAR(6); DECLARE stringValue VARCHAR(10); SET year = SUBSTRING(id, 1, 2); SET balance = SUBSTRING(id, 3, 6); IF(year <= 96) THEN SET stringValue = CONCAT('20', year, balance); ELSE SET stringValue = CONCAT('19', year, balance); END IF; RETURN stringValue; END// DELIMITER ; 

This can be simplified a bit:

 DELIMITER // CREATE FUNCTION custom_sort(id VARCHAR(8)) RETURNS varchar(10) DETERMINISTIC BEGIN IF(SUBSTRING(id, 1, 2) <= '96') THEN RETURN CONCAT('20', id); ELSE RETURN CONCAT('19', id); END IF; END// DELIMITER ;