Join tables on a partial string match input: I have a table containing (any number of) user-supplied strings:

╔═══════╗
║ Value ║
╠═══════╣
║ To    ║
║ An    ║
╚═══════╝


And a table of user details:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
║     32 ║ Dina     ║
║      2 ║ Mark     ║
╚════════╩══════════╝


I need a query to return all records from the users table where UserName is a partial string match for any Value record in the input table.  Expected output in this case is:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝
This should do what you want:

DECLARE @Users AS TABLE
(
    UserID      integer,
    UserName    nvarchar(50)
);

INSERT @Users
    (UserID, UserName)
VALUES
    (12, N'Tom'),
    (132, N'Ann'),
    (32, N'Dina'),
    (2, N'Mark');

DECLARE @Input AS TABLE
(
    Value    nvarchar(500) NOT NULL
);

INSERT @Input (Value)
VALUES 
    (N'To'),
    (N'An');

SELECT * 
FROM @Users AS u 
WHERE 
    EXISTS 
    ( 
        SELECT * 
        FROM @Input AS i 
        WHERE 
            u.UserName LIKE N'%' + i.Value + N'%'
    );


Output:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝