Delimit string to search in SQL

Question

Wondering if there is a way to delimit string to search in sql. I primarily use notepad++ to delimit string, but is there a way to accomplish this in SQL? In this example, I delimit the string with (',') in notepad++ which I can then place in a sql query.

Example via Notepad ++:

AAAA
BBBB
CCCC
DDDD
EEEE

Delimited string via notepad++:

AAAA','BBBB','CCCC','DDDD','EEEE

SQL query

SELECT ID
FROM test.dbo.testtable
WHERE id in ('AAAA','BBBB','CCCC','DDDD','EEEE')

Answer 1

This one works in MySql 8 but I think it has room for performance improvement.

The result of * from split_string are

part
AAAA
BBBB
CCCC
DDDD
EEEE

set @originalString = 'AAAA BBBB CCCC DDDD EEEE';

SELECT ID FROM test.dbo.testtable WHERE id in
(WITH RECURSIVE split_string AS (
    SELECT
        SUBSTRING_INDEX(@originalString, ' ', 1) AS part,
        SUBSTRING(
            @originalString,
            LENGTH(SUBSTRING_INDEX(@originalString, ' ', 1)) + 2
        ) AS rest
    UNION ALL
    SELECT
        SUBSTRING_INDEX(rest, ' ', 1),
        SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ' ', 1)) + 2)
    FROM
        split_string
    WHERE
        rest <> ''
) SELECT part FROM split_string);