create table table1
(
    NAME varchar(60),
    SeriesID varchar(160)
)

Insert into table1
values ('A1', '398,412,411'),
       ('A2', '123,516,203')

SELECT NAME, SeriesID
FROM TABLE1
WHERE SeriesID LIKE '%' + (Subquery) + '%'

DECLARE @table1 TABLE
    (
      NAME VARCHAR(60) ,
      SeriesID VARCHAR(160)
    )

INSERT  INTO @table1
VALUES  ( 'A1', '398,412,411' ),
        ( 'A2', '123,516,203' ),
        ( 'A3', '322,123,324' )

DECLARE @table2 TABLE ( ID VARCHAR(50) )

INSERT  INTO @table2
VALUES  ( '398' ) ,
        ( '322' )

SELECT DISTINCT
        *
FROM    @table1
WHERE   EXISTS ( SELECT 1
                 FROM   @table2
                 WHERE  SeriesID LIKE '%' + ID + '%' )

DECLARE @table1 TABLE
    (
      NAME VARCHAR(60) ,
      SeriesID VARCHAR(160)
    )

INSERT  INTO @table1
VALUES  ( 'A1', '398,412,411' ),
        ( 'A2', '123,516,203' ),
        ( 'A3', '322,123,324' )

DECLARE @table2 TABLE ( ID VARCHAR(50) )

INSERT  INTO @table2
VALUES  ( '398|322' )

DECLARE @sqlnet SQLNET = SQLNET::New('Regex.IsMatch(input, pattern)')
SELECT DISTINCT
        *
FROM    @table1
WHERE   EXISTS ( SELECT 1
                 FROM   @table2
                 WHERE  @sqlnet.Val('input', SeriesID).Val('pattern', ID).EvalBit() = 1 )

SELECT NAME, SeriesID
FROM TABLE1
WHERE ',' + SeriesID + ',' LIKE '%,' + (Subquery) + '%'

SELECT * FROM table WHERE field REGEXP '1740|1938|1940';

(select group_concat(myColumn separator '|') from..)

declare @query  nvarchar(max) = 'SELECT NAME,SeriesID FROM TABLE1 WHERE SeriesID in (' + @Subquery + ')'