Join tables on a partial string match

Question

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      ║
╚════════╩══════════╝

Paul White · Accepted Answer · 2012-12-05T14:07:51.883

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      ║
╚════════╩══════════╝

score 5 · Answer 2 · edited Apr 13 '17 at 12:42

Based on the context from your previous question SQL query for combinations without repitition I think you are looking for a way to find combinations of users and include both the name and ID in the result set. The following script demonstrates one way to achieve that:

Sample data:

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');

Load the source data into a table set up to make it easier to find combinations:

-- Working table to find combinations
DECLARE @Combination AS TABLE 
(
    item_id     tinyint IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    item        nvarchar(500) NOT NULL,
    item_value  integer NOT NULL,
    bit_value   AS 
                CONVERT
                (
                    integer, 
                    POWER(2, item_id - 1)
                )
                PERSISTED UNIQUE CLUSTERED
);    

-- Add user details to the working table
INSERT @Combination
    (item, item_value)
SELECT
    u.UserName,
    u.UserID
FROM @Users AS u;

Use the fact that the natural integers have a pattern of bits that match the combinations we want to find. Determine the maximum integer value we need based on the number of items in the working table:

-- Maximum integer needed for combination bit values
DECLARE 
    @max integer = 
    POWER(2,
        (
            SELECT COUNT_BIG(*) 
            FROM @Combination
        )
    ) - 1;

Instead of concatenating group members using FOR XML PATH, list the combinations in rows with a group_id to identify each group:

-- Find combinations
SELECT
    group_id = N.n,
    c.item,
    c.item_value
FROM @Combination AS c
JOIN dbo.Numbers AS N ON
    n.n & c.bit_value = c.bit_value
WHERE
    N.n BETWEEN 1 AND @max
ORDER BY
    group_id;

Partial output:

╔══════════╦══════╦════════════╗
║ group_id ║ item ║ item_value ║
╠══════════╬══════╬════════════╣
║        1 ║ Tom  ║         12 ║
║        2 ║ Ann  ║        132 ║
║        3 ║ Ann  ║        132 ║
║        3 ║ Tom  ║         12 ║
║        4 ║ Dina ║         32 ║
║        5 ║ Dina ║         32 ║
║        5 ║ Tom  ║         12 ║
║        6 ║ Ann  ║        132 ║
║        6 ║ Dina ║         32 ║
║        7 ║ Dina ║         32 ║
║        7 ║ Ann  ║        132 ║
║        7 ║ Tom  ║         12 ║
║        8 ║ Mark ║          2 ║
║        9 ║ Mark ║          2 ║
║        9 ║ Tom  ║         12 ║
╚══════════╩══════╩════════════╝

score 1 · Answer 3 · edited Dec 04 '12 at 14:18

Can you be more specific in what your trying to accomplish? Thomas is right, it does seem like you want to take a procedural approach to something that can be done in a set based manner.

If your looking to get a set of results and then process them one at a time for an application, I strongly recommend doing that row based processing in your application. SQL Server is designed to work with sets and not rows.

If your looking to have a procedure that returns the next row each time it is called. Again, I would suggest pulling the result set as a whole and processing the row based operations in your application code.

If you must do this in t-sql, then a cursor is what you would need. I strongly recommend against this however since a cursor is a row by agonizing row operation and cursors are not very performant.

Join tables on a partial string match

3 Answers3