Compairing two strings in MYSQL-CodePudding

I have a sequence of 20 numbers from 0 to 2, I want to compare this string with other sequences saved in my database, the problem is that the lenght of the strings saved on the database fluctuates.Also the comparison needs to be done from the end to the start. Example of what I want:

20 digits string:

'1,1,2,1,2,1,0,1,2,1,2,1,0,1,2,1,1,1,2,1'

couple of strings saved in the database:

1 - '1,1,2,1'

2 - '2,1,2,2,2,2'

3 - '2,1'

4 - '1,1,2,1,2,1'

In this case the query would return the 1 and 3 only

CodePudding user response：

create table mytable ( s varchar(60) );

insert into mytable values
('1,1,2,1'),
('2,1,2,2,2,2'),
('2,1'),
('1,1,2,1,2,1');


set @x = '1,1,2,1,2,1,0,1,2,1,2,1,0,1,2,1,1,1,2,1';

select s from mytable
where right(@x, length(s)) = s;

Output:

s
1,1,2,1
2,1

Fiddle: https://www.db-fiddle.com/f/r5m2hPbnmUu5VQfYvMVtir/0

CodePudding user response：

You could use a LIKE trick here. For example, to check for the first string 1,1,2,1:

SELECT *
FROM yourTable
WHERE ',1,1,2,1,2,1,0,1,2,1,2,1,0,1,2,1,1,1,2,1,' LIKE '%,1,1,2,1,%';