-- This example searches for the exact number 23 in a list of numbers
-- under the "ids" key in a JSON object stored as a string.
-- Ensure collation of connection is same as database else setting of user
-- variables may yield "Illegal mix of collations" error, especially if
-- database uses case-sensitive collation
-- Note that @@SESSION.collation_database may be different
-- from @@GLOBAL.collation_database
SET collation_connection = (SELECT @@SESSION.collation_database);
SHOW VARIABLES LIKE 'collation%';
-- If collation not set above, COLLATE must be used when setting string
-- variables, e.g.: SET @MY_VAR = 'my value' COLLATE utf8mb4_0900_as_cs
SET @PATTERN = '"ids":(\\[|\\[[\\d,]*\\d+,)23(\\]|,\\d+[\\d,]*\\])';
-- Basic checks
SELECT 1 AS answer, '{"ids":[23]}' REGEXP @PATTERN AS is_match;
SELECT 0 AS answer, '' REGEXP @PATTERN AS is_match;
SELECT 0 AS answer, '{"ids":[89],"test":[23]}' REGEXP @PATTERN AS is_match;
-- Do not match numbers that contain the number being searched for
SELECT 0 AS answer, '{"ids":[123]}' REGEXP @PATTERN AS is_match;
SELECT 0 AS answer, '{"ids":[234]}' REGEXP @PATTERN AS is_match;
-- Cater for numbers before and after the number being searched for
SELECT 1 AS answer, '{"ids":[1,23,45]}' REGEXP @PATTERN AS is_match;
SELECT 1 AS answer, '{"ids":[1,23]}' REGEXP @PATTERN AS is_match;
SELECT 1 AS answer, '{"ids":[0,1,23]}' REGEXP @PATTERN AS is_match;
SELECT 1 AS answer, '{"ids":[23,45]}' REGEXP @PATTERN AS is_match;
SELECT 1 AS answer, '{"ids":[23,45,67]}' REGEXP @PATTERN AS is_match;
