sql - Convert MySQL Instr command to PostgreSQL

Question

SELECT 
  SUBSTR(g1.moves, LENGTH('%s') + %d, INSTR(SUBSTR(g1.moves, LENGTH('%s') + %d, LENGTH(g1.moves)), ',') - 1)
, COUNT(*) 
FROM game g1 
WHERE 
   g1.moves LIKE '%s%%' 
   AND (outcome = '%s') 
GROUP BY substr(g1.moves, LENGTH('%s') + %d, INSTR(substr(g1.moves, LENGTH('%s') + %d, LENGTH(g1.moves)), ',') - 1) 
ORDER BY 2 DESC

I have this MySQL command. My database is in PostgreSQL, so I need to convert it, however I cannot find a good way to replace the Instr function.

The structure of SQL table is as follows:

For example, let us say I have this chess game: 'e4, e5, Nf3, Nc6'

My query would essentially say, SELECT //The Next Move// where the first moves are 'e4, e5, Nf3'

question from:https://stackoverflow.com/questions/65848626/convert-mysql-instr-command-to-postgresql

Answer 1

I would convert the string into an array, then you can easily extract the first three moves from that:

select (regexp_split_to_array(moves, ',s*'))[:3] as first_moves, count(*)
from chess
group by first_moves;

The [:3] part selects the first three elements of the array - you can adjust that to the number of moves you want to look at.

I am not sure what g1.moves LIKE '%s%%' is supposed to do. If you only want to select games starting with specific moves, you can use:

where (regexp_split_to_array(moves, ',s*'))[:3] = array['e4','e5','Nf3']

You can ditch the whole regexp_split_to_array() overhead if you declare the moves column as an array text[].

---

If you want to pass the comparison values as a parameter through JDBC you can pass a string "{e4,e5,Nf3}" and use

where (regexp_split_to_array(moves, ',s*'))[:3] = cast(? as text[])