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:
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:
That's all there is to it! Hope this has been useful for you.