MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl(id int, myTxt TEXT);
insert into tbl values
  (1, 'this is a test string with x12345 and y1264'), 
  (2, 'this is a test xtest ytest string with x11111 and y11111 and x22222 and y22222');

Records: 2  Duplicates: 0  Warnings: 0


SELECT id,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY W.rowid) num_order, 
       W.xy_val
FROM tbl
CROSS JOIN JSON_TABLE
  (
    CONCAT('["', REPLACE(myTxt, ' ', '","'), '"]'), 
    '$[*]' 
    COLUMNS 
      (
       rowid FOR ORDINALITY, 
       xy_val VARCHAR(32) PATH '$'
      )
  ) W
WHERE  W.xy_val REGEXP '[x][0-9]' OR W.xy_val REGEXP '[y][0-9]'


id
num_order
xy_val


1
1
x12345

1
2
y1264

2
1
x11111

2
2
y11111

2
3
x22222

2
4
y22222


SELECT W.xy_val
FROM tbl
CROSS JOIN JSON_TABLE
  (
    CONCAT('["', REPLACE(myTxt, ' ', '","'), '"]'), 
    '$[*]' 
    COLUMNS 
      (
       rowid FOR ORDINALITY, 
       xy_val VARCHAR(32) PATH '$'
      )
  ) W
WHERE  W.xy_val REGEXP '[x][0-9]' OR W.xy_val REGEXP '[y][0-9]'


xy_val


x12345

y1264

x11111

y11111

x22222

y22222