SQL: Converting A String Of ID's Into A String Of Values

Part of a project I was working on recently required me to convert a delimited string of Id's passed in a parameter of a URL. A string of comma delimited Names that were associated with the Id's would be returned on a form. The data was being retrieved from a SQL Server database. The way I ended up doing this was to create a function that I would pass the string into and return a temporary table of Id's. Then I would select the names into a temporary table from the Clients table where id's equaled the id's returned in the function. Finally I would convert the temporary table to a comma delimited string of names. Let me give the detail of what I did.

 Let's first create a basic client table for demonstration purposes. 


CREATE TABLE [dbo].[Clients](

       [ID] [nvarchar](18) NULL,

       [NAME] [nvarchar](255) NULL

)

 It will look something like this:

Table

Next we will create our Table-Valued Function: 


CREATE FUNCTION [dbo].[DelimStrToTable](@InStr VARCHAR(MAX))

RETURNS @TempTab TABLE

(id nvarchar(max) not null)

AS

BEGIN

    -- Ensure input ends with comma

  SET @InStr = REPLACE(@InStr + ',', ',,', ',')

DECLARE @SP INT

DECLARE @VALUE VARCHAR(1000)

WHILE PATINDEX('%,%', @INSTR ) <> 0

BEGIN

   SELECT  @SP = PATINDEX('%,%',@INSTR)

   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)

   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')

   INSERT INTO @TempTab(id) VALUES (Ltrim(Rtrim(@VALUE)))

END

       RETURN

END

 What we are doing is looping through each value in the string and inserting them into a temporary table. When the table is completed it is then returned so it can be joined with some other data. To use this function we would do something like this: 


SELECT Id, Name into #temp1 FROM Clients WHERE Id IN
(SELECT * FROM [dbo].[DelimStrToTable]('001G000001oLFyVIAW,001G000001oLI9tIAG'))

 We are selecting the Id and Name from the Clients table where the Id's equal the 2 Id's we are passing through our function. Our Id and Name are put into a temp table. Since we need these names in a comma delimited string, we would then do this: 


declare @results varchar(max)

select @results = coalesce(@results + ',', '') +  convert(varchar(250),Name)

from #temp1

order by id

select @results as results

 When you put it all together the outcome should look like this:

results

That's all there is to it! Hope this has been useful for you. 

Author: Christian Apostolu
Christian Apostolu's picture
About:

Owner/founder of Crush The Soul, software developer, amateur athlete, frustrated musician. 

"Once we accept our limits, we go beyond them." - Albert Einstein

 

Category: