Search code examples
mysqlchess

MySQL query to select all or portion of search string

I am working on a script to search chess opening positions.

I need to build a query that will select based on some PGN code or moves that are within the moves in the PGN.

example:

  1. 1.e4 2. e5
  2. 1.e4 2. e5 Nc6
  3. 1.e4 2. e5 Nc6 3. Bb5
  4. 1.e4 2. e5 Nc6 3. Bb5 a6
  5. 1.e4 2. e5 Nc6 3. Bb5 a6 4. Ba4 Nf6
  6. 1.e4 2. e5 Nc6 3. Bb5 a6 4. Ba4 Nf6 5.Nc3

Now, What I would like to do is do a search on "1.e4 2. e5 Nc6 3. Bb5 a6" and get rows 1 - 4 from above.

I know about the LIKE call but, from what I understand, it would select all of these lines.

Sorry, I don't have attempts. I have no idea what MySQL functionality that can accomplish this.

In the table, there is a column VARCHAR(200) in each row that holds one of the lines above.

Also, if anyone knows of a MySQL dB that is designed to search ECO codes based on some PGN, please feel free to post a link.

Solution

  • Assuming your have a table 'chess' with a field 'moves' varchar(200), I am using the following queries to setup your test data:

    create table chess (moves varchar(200));
insert into chess values ("1.e4 2. e5");
insert into chess values ("1.e4 2. e5 Nc6");
insert into chess values ("1.e4 2. e5 Nc6 3. Bb5");
insert into chess values ("1.e4 2. e5 Nc6 3. Bb5 a6");
insert into chess values ("1.e4 2. e5 Nc6 3. Bb5 a6 4. Ba4 Nf6");
insert into chess values ("1.e4 2. e5 Nc6 3. Bb5 a6 4. Ba4 Nf6 5.Nc3");

    Now try the following query:

    SELECT moves FROM chess 
WHERE "1.e4 2. e5 Nc6 3. Bb5 a6" LIKE concat(moves, "%");

    Note how the like comparison uses the constant on left side and actual field on right side.

    On a related note, varchar(200) is not going to be enough. Please consider increasing it or using text data type.