CODEWITHSUNDEEP
mysql
Yassir Ennazk
Yassir Ennazk

Reputation: 7169

Compare two strings according to defined values in a query

I have to compare two string fields containing letters but not alphabetically.

I want to compare them according to this order :

"J" "L" "M" "N" "P" "Q" "R" "S" "T" "H" "V" "W" "Y" "Z"

So if I compare H with T, H will be greater than T (unlike alphabetically)

And if I test if a value is greater than 'H' (> 'H') I will get all the entries containing the values ("V" "W" "Y" "Z") (again, unlike alphabetical order)

How can I achieve this in one SQL query?

Thanks

Upvotes: 2

Views: 643

Answers (2)

fthiella
fthiella

Reputation: 49049

SELECT *
FROM yourtable
WHERE
  FIELD(col, 'J', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'H', 'V', 'W', 'Y', 'Z') >
  FIELD('H', 'J', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'H', 'V', 'W', 'Y', 'Z')
         ^ your value

Or also:

SELECT *
FROM yourtable
WHERE
  LOCATE(col, 'JLMNPQRSTHVWYZ')>
  LOCATE('H', 'JLMNPQRSTHVWYZ')

Please see fiddle here.

Upvotes: 3

mpen
mpen

Reputation: 282805

You can do

SELECT ... FROM ... ORDER BY yourletterfield='J' DESC, yourletterfield='L' DESC, yourletterfield='M' DESC, ...

The equality operator will evaluate to "1" when it's true, "0" when false, so this should give you the desired order.

There's actually a FIELD() function that will make this a bit less verbose. See this article for details.

SELECT ... FROM ... ORDER BY FIELD(yourletterfield, 'J', 'L', 'M', ...)

Upvotes: 0

Related Questions