MySQL String Position and Substring sort

Question

Example data to sort:

xy3abc
y3bbc
z3bd

Sort order must be abc, bbc, bd regardless of what is before the numeral.

I tried:

SELECT 
  *, 
  LEAST(
    if (Locate('0',fcccall) >0,Locate('0',fcccall),99),
    if (Locate('1',fcccall) >0,Locate('1',fcccall),99),
    if (Locate('2',fcccall) >0,Locate('2',fcccall),99),
    if (Locate('3',fcccall) >0,Locate('3',fcccall),99),
    if (Locate('4',fcccall) >0,Locate('4',fcccall),99),
    if (Locate('5',fcccall) >0,Locate('5',fcccall),99),
    if (Locate('6',fcccall) >0,Locate('6',fcccall),99),
    if (Locate('7',fcccall) >0,Locate('7',fcccall),99),
    if (Locate('8',fcccall) >0,Locate('8',fcccall),99),
    if (Locate('9',fcccall) >0,Locate('9',fcccall),99)
  ) as locationPos,
  SUBSTRING(fcccall,locationPos,3) as fccsuffix
FROM memberlist
ORDER BY locationPos, fccsuffix

but locationPos gives me an error on the substring function call

Can you explain what you are trying to do? Sample data and desired results would be helpful. — Gordon Linoff
– Gordon Linoff, Commented Feb 2, 2016 at 16:54
Why are you ordering by locationPos first before fccsuffix? This would give you (bbc, bd, abc). — Arth
– Arth, Commented Feb 2, 2016 at 17:16

spencer7593 · Accepted Answer · 2016-02-02 17:05:18Z

1

It's not possible to reference that expression by its alias locationPos, within another expression in the same SELECT list.

Replicating the entire expression would be the SQL way to do it. (Yes, it is ugly repeating that entire expression.)

Another (less performant) approach is to use your query (minus the fccsuffix expression) as an inline view. The outer query can reference the assigned locationPos alias as a column name.

As a simple example:

SELECT v.locationPos
  FROM ( SELECT 'my really big expression' AS locationPos
           FROM ...
       ) v

This approach of using an inline view ("derived table") can have some serious performance implications with large sets.

But for raw performance, repeating the expression is the way to go:

SELECT *
     , LEAST(
        if (Locate('0',fcccall) >0,Locate('0',fcccall),99),
        if (Locate('1',fcccall) >0,Locate('1',fcccall),99),
        if (Locate('2',fcccall) >0,Locate('2',fcccall),99),
        if (Locate('3',fcccall) >0,Locate('3',fcccall),99),
        if (Locate('4',fcccall) >0,Locate('4',fcccall),99),
        if (Locate('5',fcccall) >0,Locate('5',fcccall),99),
        if (Locate('6',fcccall) >0,Locate('6',fcccall),99),
        if (Locate('7',fcccall) >0,Locate('7',fcccall),99),
        if (Locate('8',fcccall) >0,Locate('8',fcccall),99),
        if (Locate('9',fcccall) >0,Locate('9',fcccall),99)
       ) AS locationPos
     , SUBSTRING(fcccall
       , LEAST(
          if (Locate('0',fcccall) >0,Locate('0',fcccall),99),
          if (Locate('1',fcccall) >0,Locate('1',fcccall),99),
          if (Locate('2',fcccall) >0,Locate('2',fcccall),99),
          if (Locate('3',fcccall) >0,Locate('3',fcccall),99),
          if (Locate('4',fcccall) >0,Locate('4',fcccall),99),
          if (Locate('5',fcccall) >0,Locate('5',fcccall),99),
          if (Locate('6',fcccall) >0,Locate('6',fcccall),99),
          if (Locate('7',fcccall) >0,Locate('7',fcccall),99),
          if (Locate('8',fcccall) >0,Locate('8',fcccall),99),
          if (Locate('9',fcccall) >0,Locate('9',fcccall),99)
         ),3
       ) AS fccsuffix
  FROM memberlist
 ORDER BY locationPos, fccsuffix

Unfortunately, with MySQL, it's not possible to reference the result of the locationPos column within an expression in the same SELECT list.

edited Feb 2, 2016 at 17:05

answered Feb 2, 2016 at 16:57

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kc3ase Over a year ago

Wow, I can't believe it has to be nested like that. Such a shame. I suppose it gives argument to creating a function to do this and I have only 100 records or so and understand the performance hit.

spencer7593 Over a year ago

(Other databases support what you are looking for. Teradata SQL lets us use NAMED in place of AS, and then we can reference the alias in other expressions. (I think under the covers Teradata expands out the reference to the NAMED alias, as if we had typed out that whole expression again, like in my example of replicating the expression.)

spencer7593 Over a year ago

Fortunately, you can reference the expressions by their alias in the ORDER BY clause. How ugly would that be, having to repeat the expressions again. With only 100 rows, the performance of the inline view isn't likely to be an issue. Where it becomes a concern is very large sets. (It gets expensive to materialize a huge "derived table", and then turn around and run a query against it, and then perform a sort operation.) Writing a function is an option, but for raw performance, you can't beat the native builtin functions. With only 100 rows, you won't notice a difference.

kc3ase Over a year ago

For the current record set the server can handle it in less than 0.2 milliseconds. Thanks for your help!

Arth · Accepted Answer · 2016-02-02 17:29:22Z

0

For only one numeral I like:

  SELECT *
    FROM memberlist
ORDER BY SUBSTRING(fcccall, 
           LOCATE('0',fcccall)+
           LOCATE('1',fcccall)+
           LOCATE('2',fcccall)+
           LOCATE('3',fcccall)+
           LOCATE('4',fcccall)+
           LOCATE('5',fcccall)+
           LOCATE('6',fcccall)+
           LOCATE('7',fcccall)+
           LOCATE('8',fcccall)+
           LOCATE('9',fcccall),3)

But the sensible approach is not to store two separate bits of information in one field.

edited Feb 2, 2016 at 17:29

answered Feb 2, 2016 at 17:21

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Collectives™ on Stack Overflow

MySQL String Position and Substring sort

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related