2

I have a table with a username as unique & primary key.

I ran this Query to order them in ascending order

ALTER TABLE  `table` ORDER BY  `Username` ; 

So the rows are ordered like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho16793 | test1    |
| soho4595  | test2    |
| soho504   | test3    |
| soho931   | test4    |
+-----------+----------+

But I want them like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho504   | test3    |
| soho931   | test4    |
| soho4595  | test2    |
| soho16793 | test1    |
+-----------+----------+

How can I achieve this ?

5

I created a table called "billy".

mysql> SELECT * FROM billy ORDER BY f1;
+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho1234 | test4 |
| soho222  | test3 |
+----------+-------+
5 rows in set (0.00 sec)

which doesn't give the correct sort order - 1234 is bigger than 222!

Then, from here, I got the SUBSTRING() function and formulated the following SQL:

mysql> SELECT CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED) 
AS fred, f2 FROM billy ORDER BY fred ASC;

which gives you the integers you require to sort on ("fred" is an alias for the new integer column):

+------+-------+
| fred | f2    |
+------+-------+
|   10 | test5 |
|   12 | test1 |
|  123 | test2 |
|  222 | test3 |
| 1234 | test4 |
+------+-------+
5 rows in set (0.00 sec)

mysql>

This also helped for CASTing. And thanks to @oNare for his question in comments below which led me to change this post from using the MID() function to the more standard SUBSTRING().

And the answer you require is given by

SELECT * FROM billy ORDER BY CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED) ASC;

+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho222  | test3 |
| soho1234 | test4 |
+----------+-------+
5 rows in set (0.00 sec)

mysql>
  • But what if the length of the varchar string add 3 more rows to so23,so13,so2, it won't give the expected result with soho* and so*. (Curious). +1, btw. – oNare Aug 13 '15 at 23:03
  • Thanks for your input @oNare - your question caused me to change the MID() function which I had previously used (and thought strangely named...) to the more standard SUBSTRING() - obvious really. And the answer to your question is that, yes, it can be done using MySQL SQL only - but the SQL is so horrendous that it won't fit in a comment - ask it as a question and I'll answer :-) – Vérace Aug 14 '15 at 12:21
  • Tag me when you do it :] – oNare Aug 14 '15 at 12:23
  • @oNare - do what? Forget that - I just noticed your added answer - check mine out! – Vérace Aug 14 '15 at 12:44
  • Don't worry, I'm not trying to take any credit, I just was curious. – oNare Aug 14 '15 at 13:16
1

I was curious about how Vérace's query is going to sort different varchar's f1 (his tables) length, so, It didn't work as expected. I decided to make my own sort using LOCATE,LEFT and (the one I hate) REGEXP.

New data:

mysql> SELECT * FROM billy ORDER BY f1;
+-----------+-------+
| f1        | f2    |
+-----------+-------+
| on13      | 12    |
| on2       | 12    |
| on24      | 12    |
| onaree123 | test3 |
| onaree22  | 12    |
| soho10    | test5 |
| soho12    | test1 |
| soho123   | test2 |
| soho1234  | test4 |
| soho22223 | test6 |
| to13      | NULL  |
| to2       | NULL  |
| to4       | NULL  |
+-----------+-------+
13 rows in set (0.00 sec)

If you see there's a mess with the natural sorting because f1 column VARCHAR.

Testing Vérace's query:

mysql> SELECT * FROM billy ORDER BY CAST(MID(f1, 5, LENGTH(f1)) AS UNSIGNED) ASC;
+-----------+-------+
| f1        | f2    |
+-----------+-------+
| on13      | 12    |
| to2       | NULL  |
| to13      | NULL  |
| to4       | NULL  |
| onaree22  | 12    |
| onaree123 | test3 |
| on24      | 12    |
| on2       | 12    |
| soho10    | test5 |
| soho12    | test1 |
| soho123   | test2 |
| soho1234  | test4 |
| soho22223 | test6 |
+-----------+-------+
13 rows in set, 8 warnings (0.00 sec)

mysql> 

It didn't work as expected, so, I tried this:

SELECT 
    billy.* 
FROM billy 
ORDER BY # ORDER BY LETTERS 
        LEFT(LOWER(f1),LOCATE(CASE WHEN LOWER(f1) REGEXP '^[a-z]*1' IS TRUE THEN 1
                                   WHEN LOWER(f1) REGEXP '^[a-z]*2' IS TRUE THEN 2
                                   WHEN LOWER(f1) REGEXP '^[a-z]*3' IS TRUE THEN 3
                                   WHEN LOWER(f1) REGEXP '^[a-z]*4' IS TRUE THEN 4
                                   WHEN LOWER(f1) REGEXP '^[a-z]*5' IS TRUE THEN 5
                                   WHEN LOWER(f1) REGEXP '^[a-z]*6' IS TRUE THEN 6
                                   WHEN LOWER(f1) REGEXP '^[a-z]*7' IS TRUE THEN 7
                                   WHEN LOWER(f1) REGEXP '^[a-z]*8' IS TRUE THEN 8
                                   WHEN LOWER(f1) REGEXP '^[a-z]*9' IS TRUE THEN 9
                                   WHEN LOWER(f1) REGEXP '^[a-z]*0' IS TRUE THEN 0 END,LOWER(f1))-1) ASC,
        # ORDER BY INTERGERS
        CAST(MID(LOWER(f1),LOCATE(CASE WHEN LOWER(f1) REGEXP '^[a-z]*1' IS TRUE THEN 1
                               WHEN LOWER(f1) REGEXP '^[a-z]*2' IS TRUE THEN 2
                               WHEN LOWER(f1) REGEXP '^[a-z]*3' IS TRUE THEN 3
                               WHEN LOWER(f1) REGEXP '^[a-z]*4' IS TRUE THEN 4
                               WHEN LOWER(f1) REGEXP '^[a-z]*5' IS TRUE THEN 5
                               WHEN LOWER(f1) REGEXP '^[a-z]*6' IS TRUE THEN 6
                               WHEN LOWER(f1) REGEXP '^[a-z]*7' IS TRUE THEN 7
                               WHEN LOWER(f1) REGEXP '^[a-z]*8' IS TRUE THEN 8
                               WHEN LOWER(f1) REGEXP '^[a-z]*9' IS TRUE THEN 9
                               WHEN LOWER(f1) REGEXP '^[a-z]*0' IS TRUE THEN 0 END,LOWER(f1))) AS UNSIGNED) ASC;

And I got the desired result.

Testing the monster sort:

mysql> SELECT 
    -> billy.* 
    -> FROM billy 
    -> ORDER BY # ORDER BY LETTERS 
    -> LEFT(LOWER(f1),LOCATE(CASE WHEN LOWER(f1) REGEXP '^[a-z]*1' IS TRUE THEN 1
    -> WHEN LOWER(f1) REGEXP '^[a-z]*2' IS TRUE THEN 2
    -> WHEN LOWER(f1) REGEXP '^[a-z]*3' IS TRUE THEN 3
    -> WHEN LOWER(f1) REGEXP '^[a-z]*4' IS TRUE THEN 4
    -> WHEN LOWER(f1) REGEXP '^[a-z]*5' IS TRUE THEN 5
    -> WHEN LOWER(f1) REGEXP '^[a-z]*6' IS TRUE THEN 6
    -> WHEN LOWER(f1) REGEXP '^[a-z]*7' IS TRUE THEN 7
    -> WHEN LOWER(f1) REGEXP '^[a-z]*8' IS TRUE THEN 8
    -> WHEN LOWER(f1) REGEXP '^[a-z]*9' IS TRUE THEN 9
    -> WHEN LOWER(f1) REGEXP '^[a-z]*0' IS TRUE THEN 0 END,LOWER(f1))-1) ASC,
    -> # ORDER BY INTERGERS
    -> CAST(MID(LOWER(f1),LOCATE(CASE WHEN LOWER(f1) REGEXP '^[a-z]*1' IS TRUE THEN 1
    -> WHEN LOWER(f1) REGEXP '^[a-z]*2' IS TRUE THEN 2
    -> WHEN LOWER(f1) REGEXP '^[a-z]*3' IS TRUE THEN 3
    -> WHEN LOWER(f1) REGEXP '^[a-z]*4' IS TRUE THEN 4
    -> WHEN LOWER(f1) REGEXP '^[a-z]*5' IS TRUE THEN 5
    -> WHEN LOWER(f1) REGEXP '^[a-z]*6' IS TRUE THEN 6
    -> WHEN LOWER(f1) REGEXP '^[a-z]*7' IS TRUE THEN 7
    -> WHEN LOWER(f1) REGEXP '^[a-z]*8' IS TRUE THEN 8
    -> WHEN LOWER(f1) REGEXP '^[a-z]*9' IS TRUE THEN 9
    -> WHEN LOWER(f1) REGEXP '^[a-z]*0' IS TRUE THEN 0 END,LOWER(f1))) AS UNSIGNED) ASC;
+-----------+-------+
| f1        | f2    |
+-----------+-------+
| on2       | 12    |
| on13      | 12    |
| on24      | 12    |
| onaree22  | 12    |
| onaree123 | test3 |
| soho10    | test5 |
| soho12    | test1 |
| soho123   | test2 |
| soho1234  | test4 |
| soho22223 | test6 |
| to2       | NULL  |
| to4       | NULL  |
| to13      | NULL  |
+-----------+-------+
13 rows in set (0.00 sec)

mysql> 

Hope this help to feed my curiosity hunger.

EDIT 2:

From HERE, I made two functions: alpha and num, this is just the same query without the REGEXP monstrosity and better performance:

mysql> SELECT
    -> test.alpha(f1) AS CHARs,
    -> test.num(f1) AS INTs,
    ->     billy.* 
    -> FROM billy 
    -> ORDER BY # ORDER BY LETTERS 
    ->         CAST(test.alpha(f1) AS CHAR) ASC,
    ->         # ORDER BY INTERGERS
    ->         CAST(test.num(f1) AS UNSIGNED) ASC;
+--------+-------+-----------+-------+
| CHARs  | INTs  | f1        | f2    |
+--------+-------+-----------+-------+
| on     | 2     | on2       | 12    |
| on     | 13    | on13      | 12    |
| on     | 24    | on24      | 12    |
| onaree | 22    | onaree22  | 12    |
| onaree | 123   | onaree123 | test3 |
| soho   | 10    | soho10    | test5 |
| soho   | 12    | soho12    | test1 |
| soho   | 123   | soho123   | test2 |
| soho   | 1234  | soho1234  | test4 |
| soho   | 22223 | soho22223 | test6 |
| to     | 2     | to2       | NULL  |
| to     | 4     | to4       | NULL  |
| to     | 13    | to13      | NULL  |
+--------+-------+-----------+-------+
13 rows in set (0.00 sec)

mysql> 
  • Take a look at my reply. I did give you a +1 for this though - nice use of MySQL's appalling regex implementation. – Vérace Aug 15 '15 at 12:26
  • 1
    You wrote "It didn't work as expected, so, I tried this:" - I think you're being a bit unfair I feel :-( - I was asked to produce a return for a string which started 'soho' and then had an INT - my code met the requirement that the OP wished for. It was never meant to deal with the scenarios you outlined. But, I think that your expansion of the scope of the question is an interesting challenge - and i@ve answered it (in spades! :-) ). Anyway, thanks again for your input and I look forward to your response. – Vérace Aug 15 '15 at 12:34
0

Following @oNare's throwing down of the SQL gauntlet, verily I resolved to take up the challenge to my SQL-hood and thus, in this the year of our Lord two thousand and fifteen, I thusly formulated the following palimpsest. I offered incantations to the SQL muses, received inspiration from the SQL daemons, had it carved into silicon and presented it to the SQL Supreme Being, aka the MySQL Query Optimiser! And lo and behold, the result of my travails.

I considered it to be of cardinal importance to both deal with the evil beast that is the NULL. Assuredly I speaketh unto thee and tell you that it is an abomination and an offece to the fundamental law of God and man for an entity to be equal and not equal to itself and anything else at the same time! It was also a requirement that an ORDER BY clause be imposed in accordance with the wishes of the Deities resident in the heavens!

The first laboured step of my Odyssey was to CREATE a TABLE

CREATE TABLE billy (f1 VARCHAR(10), f2 VARCHAR(10), first_dig INTEGER);

INSERT INTO billy VALUES ('on13', '12', 3);
INSERT INTO billy VALUES ('on24', '12', 3);
INSERT INTO billy VALUES ('onaree123', 'test3', 7);
INSERT INTO billy VALUES ('onaree22', '12', 7);
INSERT INTO billy VALUES ('soho10', 'test5', 5);
INSERT INTO billy VALUES ('soho12', 'test1', 5);
INSERT INTO billy VALUES ('soho123', 'test2', 5);
INSERT INTO billy VALUES ('soho1234', 'test4', 5);
INSERT INTO billy VALUES ('soho22223', 'test6', 5);
INSERT INTO billy VALUES ('to13', NULL, 3);
INSERT INTO billy VALUES ('to2', NULL, 3);
INSERT INTO billy VALUES ('to4', '12', 3);
INSERT INTO billy VALUES ('xxxx', 'yyyyy', 0);
INSERT INTO billy VALUES (NULL, 'zzsdfs', NULL);
INSERT INTO billy VALUES ('sfs5436', 'blah1', 4);
INSERT INTO billy VALUES ('onar87343', 'blah2', 5);
INSERT INTO billy VALUES ('sdsd0935' , 'blah3', 5);
INSERT INTO billy VALUES ('sss8765678', 'sfs', 4);

Method 1 - read 'em and weep! A 1-liner solution!!!

From here, I garnered this little beauty.

SELECT COALESCE(CAST(REVERSE(CONVERT(REVERSE(f1), UNSIGNED)) AS UNSIGNED), 0) AS My_num from billy ORDER BY My_Num;

If you don't mind NULLs, you can leave out the COALESCE.

(@oNare, I told you that I was "planning on my response blowing you away!" :-) )

Method 1.5 - ANOTHER 1-liner!!! Can you believe it?

This is a bit of a "cheat"... :-)

I downloaded the source for MariaDB 10.0.21, compiled and installed it. Loaded my old pal "billy the table" with the script above and ran the following SQL.

SELECT f1, COALESCE(CAST(SUBSTRING(f1, REGEXP_INSTR(f1, '[0-9]')) AS UNSIGNED), 0) AS fred FROM billy;

(for some reason '[:digit:]' fails - don't understand why. Anyone?)

Another reason not to use MySQL - no decent regex support - MySQL's implementation is a joke - and it's not as if it would take a lot of work to steal a BSD (or similar) licenced library!

Method 2

From this post, I adapted the content and came up with this SQL. The reason that there's a subselect is that MySQL doesn't allow aliases in WHERE clauses. COALESCE can be used to make the query return 0 for NULL values of fred.

Strictly speaking, the IS NOT NULL is superfluous here, because fred <> 0 will eliminate NULLs also (0 is neither equal to, nor not equal to, NULL) - I put it in just to show that NULLs can be dealt with also.

SELECT t1.* 
FROM
(
  SELECT f1, fd,
  (
    CAST(SUBSTRING(f1,  
                   LEAST 
                   (
                     if (Locate('0', f1) > 0, Locate('0', f1), 999),
                     if (Locate('1', f1) > 0, Locate('1', f1), 999),
                     if (Locate('2', f1) > 0, Locate('2', f1), 999),
                     if (Locate('3', f1) > 0, Locate('3', f1), 999),
                     if (Locate('4', f1) > 0, Locate('4', f1), 999),
                     if (Locate('5', f1) > 0, Locate('5', f1), 999),
                     if (Locate('6', f1) > 0, Locate('6', f1), 999),
                     if (Locate('7', f1) > 0, Locate('7', f1), 999),
                     if (Locate('8', f1) > 0, Locate('8', f1), 999),
                     if (Locate('9', f1) > 0, Locate('9', f1), 999)
                    ),
     LENGTH(f1)) AS UNSIGNED)
   ) AS fred
   FROM billy
  ) AS t1
WHERE fred <> 0 AND fred IS NOT NULL
ORDER BY fred;

Method 3 - World's worst query ever - AKA the brute force approach :-)

I included this more as a joke than anything else.

I got this here - the original just had 10 REPLACEs - it was to eliminate digits, but I'm nothing if not flexible, so voilà:

SELECT t1.f1, t1.fd, t1.extracted
FROM
(
SELECT f1, fd,
CAST(
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(f1, 'a', ''), 
'b', ''), 
'c', ''), 
'd', ''), 
'e', ''), 
'f', ''), 
'g', ''), 
'h', ''), 
'i', ''), 
'j', ''), 
'k', ''), 
'l', ''), 
'm', ''), 
'n', ''), 
'o', ''), 
'p', ''), 
'q', ''), 
'r', ''), 
's', ''), 
't', ''), 
'u', ''), 
'v', ''), 
'w', ''), 
'x', ''),
'y', ''), 
'z', '') AS UNSIGNED)
AS extracted
FROM
    billy) as t1
WHERE extracted <> 0 AND extracted IS NOT NULL
ORDER BY extracted;

========================== FUNCTIONS ================

Sometimes a function just makes life easier - and although SQL is powerful, there are some situations where procedural logic breaks down (although, not here - I just wanted to learn something! :-)

Function 1.

This page gives this function which returns the position of the first digit in a string. The interested reader is invited to use the function to obtain a pos for a SUBSTRING() function which extracts the integer component of the f1 field of billy.

DELIMITER $$
DROP FUNCTION IF EXISTS `find_first_int`$$

CREATE FUNCTION find_first_int(pData CHAR(10))
  RETURNS INT
BEGIN
DECLARE vPos INT DEFAULT 1;
DECLARE vRes INT DEFAULT 0;
DECLARE vChar INT;
WHILE vPos <= LENGTH(pData) DO
  SET vChar = ASCII(SUBSTR(pData, vPos, 1));
  IF vChar BETWEEN 48 AND 57 THEN
    RETURN IFNULL(vPos, 0);
  END IF;
  SET vPos = vPos + 1;
END WHILE;
RETURN 0;  -- can choose RETURN NULL if desired.
END $$
DELIMITER ;

SELECT f1, find_first_int(f1) AS first_int FROM billy;

Function 2

Got this baby here. I am puzzled though (anyone answering will get a +1). It's supposed to return a string (on the right hand side of the response - strings are right-justified). But I can add integers to it, therefore confused - really grateful for a resolution! Even the f1 value of 'xxxx' which gives a blank extnum can be added to.

And what really puzzled me (cost me > 1hr of cursing/swearing/pulling_out_of_hair) is that this works for MySQL but fails for MariaDB.

DELIMITER $$

DROP FUNCTION IF EXISTS `extnum` $$
CREATE FUNCTION `extnum`(in_phone varchar(50)) RETURNS varchar(50) CHARSET latin1
    NO SQL
BEGIN
DECLARE ctrNumber varchar(50);
DECLARE finNumber varchar(50) default ' ';
DECLARE sChar varchar(2);
DECLARE inti INTEGER default 1;

    IF in_phone IS NULL THEN
      RETURN '0';
    END IF;

    IF length(in_phone) > 0 THEN
    WHILE(inti <= length(in_phone)) DO
        SET sChar = SUBSTRING(in_phone, 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 TRIM(finNumber);
    ELSE
    RETURN 0;
    END IF;

END$$
DELIMITER ;

select f1, extnum(f1), extnum(f1) + 5 AS test, cast(extnum(f1) as unsigned) as My_Num, length(extnum(f1)) from billy;
  • It did not expect too many ways, you're trying to wake out the beast inside me. First, Don't not try Method 3, don't even make that query again. Hahaha. I like the 1-2 Methods, I have 2 functions that could work fine but when I posted my answer I just thought about the possibility of many different Usernames with this condition and here what we got, 6 different ways, 4 SQL queries and 2 functions. I upvote your new answer because is an amazing work but I don't know who could downvote this, this is science and it's a rainbow in dark times. – oNare Aug 15 '15 at 13:36
  • 1
    Wow! A "rainbow in dark times."! Not even my girlfriend says things this nice about me! :-). I'd be interested in seeing your functions - if you don't want to add them to your post, you can email them (see profile) - but the whole point of this site is that people learn from each other, so I would encourage you to post them - I'd certainly like to see them and learn! Yeah, a downvote... WTF? I just wish that the person who has downvoted me would have the decency to tell me why - normally, I leave a comment when I do so (which is rarely - I more often vote to close). Hasta la proxima! – Vérace Aug 15 '15 at 17:00
  • Haha. Claro, ya edité mi respuesta agregando las funciones. Dales una hojeada. – oNare Aug 15 '15 at 17:29
  • Hola again! You say that you are adding the functions to your answer - but I can't see them (yet). Oh, yes, I've thought of a use for the (horrible) query 3 - get the INTEGER component of a string such as abc345xyz. Can't see any other way (apart obviously from MariaDB regexps). – Vérace Aug 16 '15 at 15:48
  • Hey, I edited my question yesterday. You could find them in my EDIT. I was thinking about, what if there's a multiples chars and ints like "on22are32, on2ona12, etc..". I have to try it and come with results. – oNare Aug 16 '15 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.