Question 265257 in Mysql Question posted in
The official MySQL documentation can be found here.

Sort mySQL results by the position (index) of a string in a text field

I’m sorry if this is duplicate question, but I could’nt find anything helpful here.

So, what I’m trying to do, is to sort the results of my mySQL statement by the position of a query string inside the title field. I tried to do this with the INSTR function, and this kinda works (even though the first result is wrong because it has an umlaut in it – maybe you also have an explanation for that = bonus points). My statement looks like this (query string is ‘all’ in this case):

SELECT title, translation, INSTR(title, 'all') as posititon
FROM `dictionary` 
WHERE title LIKE '%all%' 
ORDER BY posititon

The result looks like this:

enter image description here

So far, so good. But I also want to have results that match the query string in the field ‘description’. So when I add OR translation LIKE '%all%' in the WHERE clause, I get a totally different result:

SELECT title, translation, INSTR(title, 'all') as posititon
FROM `dictionary` 
WHERE (title LIKE '%all%' OR translation LIKE '%all%')
ORDER BY posititon

Result:

enter image description here

So my goal would be to sort the result PRIMARY by the position of the query string in the field ‘title’ and SECONDARY by the position of the query string in the field ‘translation’.

Can you please help me with that?

Tags:

2

Answers


  1. 0

    I think regexp_like may help for getting the position:

    
with cte as (
   select 'Mistkäll' as title 
   union all 
   select 'all' 
   union all
   select 'cow')
select 
   title, 
   regexp_instr(title,'[aä]ll')  regexp1
from cte
order by regexp1;

    output:

    title regexp1
    cow 0
    all 1
    Mistkäll 6

    Note: of course you need to expand the regular expression ([aä]) with all possible values like á, à, etc.

    Login or Signup to reply.
  2. 0

    This does not deal with the accent-sensitivity issue, addressed in Luuk’s answer.

    Because both INSTR() and REGEXP_INSTR() return 0 when the needle is not found, you need to change the 0 to something else to stop them coming first in the sort.

    Here’s one way:

    WITH dictionary (title, translation) AS (
    VALUES
        ROW('Mistkäll', 'Mistkerl'),
        ROW('all', 'alle, aufgerbraucht - '),
        ROW('Toa', 'Tor (Fußball)'),
        ROW('ball', 'something'),
        ROW('something', 'ball'),
        ROW('something', 'all'),
        ROW('small', 'something')
)
SELECT
    title,
    translation,
    NULLIF(INSTR(title, 'all'), 0) as ti_pos,
    NULLIF(INSTR(translation, 'all'), 0) as tr_pos
FROM `dictionary` 
WHERE (title LIKE '%all%' OR translation LIKE '%all%')
ORDER BY -ti_pos DESC, -tr_pos DESC;

    -ti_pos DESC is the same as ti_pos ASC but with the NULLs moved to the end.

    Outputs:

    title translation ti_pos tr_pos
    all alle, aufgerbraucht – 1 1
    ball something 2
    small something 3
    something all 1
    something ball 2
    Toa Tor (Fußball) 10
    Mistkäll Mistkerl

    Or for something where the intent is more obvious:

    SELECT
    title,
    translation,
    INSTR(title, 'all') as ti_pos,
    INSTR(translation, 'all') as tr_pos
FROM `dictionary` 
WHERE (title LIKE '%all%' OR translation LIKE '%all%')
ORDER BY IF(ti_pos = 0, 99999999, ti_pos), IF(tr_pos = 0, 99999999, tr_pos);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search