CODEWITHSUNDEEP
sqlsql-server-2012
Geoff Garcia
Geoff Garcia

Reputation: 29

How can I iterate through characters in a SQL result value?

We have a job table that includes ID and title, something like this:

JobID | JobTitle
1       president
2       vice-president
3       director

The user table includes a jobID that is supposed to map to the job table, but whoever coded it made it a multi-select field in the UI and appended values with pipes between. So a user could be a president AND a vice-president AND a director. Here is an example of the user table

UserName | JobID
Suzy       1|2|3
Bob        3
Jane       2|1

I'm trying to run a report of all staff and their titles, but am stumped at how to iterate through the multi-value jobIDs and display the jobTitle.

The query I'm currently using is something like:

select user.username, job.JobTitle 
from user 
inner join job on user.JobID = job.JobID

This is all on SQL Server 2012

Any suggestions? I'd settle for displaying additional titles on subsequent rows, or in subsequent columns, whatever is easier.

Upvotes: 0

Views: 1570

Answers (3)

Fletch
Fletch

Reputation: 367

In order to return a comma separated list of Job titles for each user you can adapt Julien's code, as below:

SELECT 
    U2.UserName, 
    SUBSTRING(
        REPLACE(
            REPLACE(
                        (
                        SELECT J.JobTitle
                        FROM [job] J
                        INNER JOIN [user] U
                        ON ('|'+U.JobID+'|' LIKE('%|'+CAST(J.JobID as varchar(20))+'|%'))
                        WHERE U.UserName = U2.UserName
                        FOR XML PATH('')
                        ), '</JobTitle>', ''
                    ), '<JobTitle>', ', '
                ),2,1000
            ) [JobTitles]
FROM [user] U2

Upvotes: 0

Julien Blanchard
Julien Blanchard

Reputation: 825

The best answer is obviously to simply split the column and create a separate table with 1 row per child (job) to have atomic data. But in the meantime, you can do something like :

SELECT [user].username, [job].JobTitle
FROM [job]
INNER JOIN [user] 
    ON ('|'+[user].JobID+'|' LIKE('%|'+CAST([job].JobID as varchar(20))+'|%'))

Oh, and before you shout after someone for inserting non-atomic data in a table, start by shunning him for using reserved keywords as table names. Never ever ever ever name your table user.

Btw, this syntax can be used to do something like :

CREATE TABLE users AS
SELECT user.username, job.jobId
FROM [job]
INNER JOIN [user] 
    ON ('|'+[user].JobID+'|' LIKE('%|'+CAST([job].JobID as varchar(20))+'|%'))

which will net you a sanitized atomic table.

Upvotes: 0

morgb
morgb

Reputation: 2312

Here is a function you can use to split a delimited field and return the results to a table (which you can then use in subsequent operations):

CREATE FUNCTION dbo.ufnGENSplitDelimField (
    @InputString nvarchar(max),
    @Delimiter nvarchar(10)
)

RETURNS @Results TABLE (
    Item nvarchar(50)
)
AS
BEGIN

-- default delimiter to comma if blank
IF ISNULL(@Delimiter,'') = ''
BEGIN
    SET @Delimiter = ','
END

DECLARE @Item nvarchar(50);
DECLARE @ItemList nvarchar(max);
DECLARE @DelimIndex int;

SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)

/*
Loop through all items, removing the first at each iteration.  For example, if we start with the string 'bob,mary,joe':
    1. set @Item = 'bob'
    2. insert 'bob' into result table
    3. set @ItemList = 'mary,joe'
    4. set @DelimIndex = 4
    loop
*/
WHILE (@DelimIndex != 0)
BEGIN
    SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
    INSERT INTO @Results VALUES (@Item)

    -- Set @ItemList = @ItemList minus one less item
    SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
    SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE

IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString, and we need to insert the final item in the list
BEGIN
    SET @Item = @ItemList
    INSERT INTO @Results VALUES (@Item)
END
ELSE -- No delimiters were encountered in @InputString, so just return @InputString
BEGIN
    INSERT INTO @Results VALUES (@InputString)
END

RETURN;
END;

Upvotes: 1

Related Questions