Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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      ║
╚════════╩══════════╝
share|improve this question
2  
What exactly are you trying to do? It seems like you're taking a procedural-based approach when this can be done set-based. –  Thomas Stringer Dec 4 '12 at 14:04
1  
This type of thing can be done using a cursor (assuming you want to loop through the values)... Can you give us some context to this question? Using cursors is normally a last resort. –  Jon Seigel Dec 4 '12 at 14:15
1  
Do what in a set based manner? Please provide example data and desired results. –  Martin Smith Dec 5 '12 at 8:42
    
@Qrosh - You want to assign the value column for each row in the table to a different variable? Does the table have a fixed number of rows? Also what do you then plan to do with the variables? –  Martin Smith Dec 5 '12 at 9:07
    
the number of rows Not fixed. The content of the row is unknown I posed the question a little more clearly better Please wait –  Qrosh Dec 5 '12 at 9:23
show 3 more comments

3 Answers

up vote 3 down vote accepted

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      ║
╚════════╩══════════╝
share|improve this answer
add comment

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.

share|improve this answer
add comment

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 ║
╚══════════╩══════╩════════════╝
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.