1

This question can be thought of as related to this. If I had the same table:

ID Name Marks
1 Ashley 81
2 Samantha 75
3 Belvet 84
4 Julia 76

And I wanted to sort on last three characters, then why wouldn't this query work:

SELECT DISTINCT Name FROM students
    where Marks > 75
    ORDER BY Name regexp "[a-z]{3}$" ASC;

Do regular expression work while sorting? When I do run this query I get no errors whatsoever but there is no sorting as well. What I get is this:

shiv_90
  • 1,025
  • 3
  • 12
  • 35
  • 1
    Add `Name regexp "[a-z]{3}$" ` to the select list and see what's returned. (Standard trick when having order by issues.) – jarlh May 14 '21 at 12:06
  • It does not return any error but returns a new column with 1s as values. – shiv_90 May 14 '21 at 12:27
  • 1
    `REGEXP` returns the **starting index** of the substring of the string expr that matches the regular expression specified by the pattern pat, or **0** if there is no match. It does not return the string. You are looking for `REGEXP_SUBSTR` [link](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp) – ontrack May 14 '21 at 12:35
  • 1
    @ontrack *REGEXP returns the starting index of the substring of the string expr that matches the regular expression specified by the pattern pat, 0 if there is no match.* Who tells this to you??? – Akina May 14 '21 at 12:36
  • I think REGEXP matches the whole string pattern. – shiv_90 May 14 '21 at 12:38

2 Answers2

1

It does work for me in Oracle.

with students (id, name, marks) as(
select 1,   'Ashley',  81 from dual union all
select 2,   'Samantha',    75 from dual union all
select 3,   'Belvet',  84 from dual union all
select 4,   'Julia' ,  76 from dual)
SELECT
    regexp_substr(name, '[a-z]{3}$')
FROM
    students
ORDER BY
    regexp_substr(name, '[a-z]{3}$');
  • 1
    Using a similar query in MySQL gives error REGEXP_SUBSTR does not exist: ```select REGEXP_SUBSTR(Name, "[a-z]{3}$") from students where Marks > 75 order by REGEXP_SUBSTR(Name, "[a-z]{3}$") ASC;``` – shiv_90 May 14 '21 at 12:22
  • https://stackoverflow.com/questions/2742650/what-is-the-equivalent-of-regexp-substr-in-mysql –  May 14 '21 at 12:30
  • 1
    Ok got it working. regexp_substr() in the order by clause. – shiv_90 May 14 '21 at 12:39
1

All values matches the pattern.

with students (id, name, marks) as(
select 1,   'Ashley',  81 from dual union all
select 2,   'Samantha',    75 from dual union all
select 3,   'Belvet',  84 from dual union all
select 4,   'Julia' ,  76 from dual)
SELECT *, 
       Name regexp "[a-z]{3}$" AS ordering_expression 
FROM students
ORDER BY Name regexp "[a-z]{3}$" ASC;

id | name     | marks | ordering_expression
-: | :------- | ----: | ------------------:
 1 | Ashley   |    81 |                   1
 2 | Samantha |    75 |                   1
 3 | Belvet   |    84 |                   1
 4 | Julia    |    76 |                   1

So the rows are not sorted.

-- test 6-letter pattern
with students (id, name, marks) as(
select 1,   'Ashley',  81 from dual union all
select 2,   'Samantha',    75 from dual union all
select 3,   'Belvet',  84 from dual union all
select 4,   'Julia' ,  76 from dual)
SELECT *, 
       Name regexp "[a-z]{6}$" AS ordering_expression 
FROM students
ORDER BY Name regexp "[a-z]{6}$" ASC;

id | name     | marks | ordering_expression
-: | :------- | ----: | ------------------:
 4 | Julia    |    76 |                   0
 1 | Ashley   |    81 |                   1
 2 | Samantha |    75 |                   1
 3 | Belvet   |    84 |                   1

db<>fiddle here

'Ashley' should come first because 'ley' < 'lia'. – shiv_90

with students (id, name, marks) as(
select 1,   'Ashley',  81 from dual union all
select 2,   'Samantha',    75 from dual union all
select 3,   'Belvet',  84 from dual union all
select 4,   'Julia' ,  76 from dual)
SELECT *
FROM students
ORDER BY RIGHT(Name, 3) ASC;

id | name     | marks
-: | :------- | ----:
 1 | Ashley   |    81
 4 | Julia    |    76
 2 | Samantha |    75
 3 | Belvet   |    84

db<>fiddle here

Akina
  • 39,301
  • 5
  • 14
  • 25