Return delimited values from function SQL Server

Question

I am having problems wrapping my head around how to return a concatenated string from a lookup table using a function. In my function I can parse the input string delimiter easy enough, however the limitations of what can and can't be done with functions has me stomped.

CREATE OR ALTER FUNCTION dbo.fn_DemographicsLookup(@input VARCHAR(100), @colName VARCHAR(50))
RETURNS VARCHAR(500) 
AS
BEGIN
-- @input is a string of numbers delimited by *
-- @colName provides the row of the table
  DECLARE @str NVARCHAR(250)
  DECLARE @rts NVARCHAR(250)

  DECLARE @qry NVARCHAR(250)
  DECLARE @tab TABLE (item varchar(100))
  DECLARE @res TABLE (item varchar(250))
  DECLARE @xml XML


  SET @str = ''
  SET @rts = ''

  SET @xml = N'<t>' + REPLACE(@input,'*','</t><t>') + '</t>'
  INSERT INTO @tab 
  SELECT r.value('.','VARCHAR(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)

  SELECT @str = (CONCAT( COALESCE(@str,''),'[',STUFF((SELECT '[' + item + '],'
            FROM @tab
            FOR XML PATH('')) ,1,1,'')))
  SELECT @str = LEFT(@str,LEN(@str) - 1) -- since the column headers are numbers...

  SET @xml = N'<t>' + REPLACE(@str,',','</t><t>') + '</t>'
  INSERT INTO @res
  SELECT r.value('.','VARCHAR(MAX)') as item
  FROM @xml.nodes('/t') as records(r)

  SELECT @rts = (CONCAT(COALESCE(@rts,''),'[',STUFF((SELECT item +',''|'','
            FROM @res
            FOR XML PATH('')),1,1,'')))

  SELECT @rts = LEFT(@rts,LEN(@rts)-5)

  DECLARE @out VARCHAR(500)
  
  SET @out = 'SELECT CONCAT('+@rts+') FROM dbo.DemographicsLookup WHERE colName = '''+@colName+''''


  -- I tried this but you can't call a stored procedure from a function
  --set @qry = 'select '+@rts+' from dbo.DemographicsLookup where colName = '''@colName+''''
  --EXEC sp_executesql @qry, N'@retVal VARCHAR(250) OUTPUT' ,@retVal = @lst OUTPUT

  RETURN @rts


END

I've tried to return @out to use in a dynamic SQL statement, but I haven't been able to figure it out so right now if I call:

select  dbo.fn_DemographicsLookup(coalesce(input,''),'input') from (select input from dbo.demographicsV2)p

I get the input string value:

12

15

2*3*16

What the output should be:

Money

Makes

The|World|Go

EDIT

CREATE TABLE dbo.DemographicsLookup
(
    colName varchar(100),
    "0" varchar(100),
    "1" varchar(100),
    "2" varchar(100),
    "3" varchar(100),
    "4" varchar(100),
    "5" varchar(100),
    "6" varchar(100),
    "7" varchar(100),
    "8" varchar(100),
    "9" varchar(100),
    "10" varchar(100),
    "11" varchar(100),
    "12" varchar(100),
    "13" varchar(100),
    "14" varchar(100),
    "15" varchar(100),
    "16" varchar(100)
)

CREATE TABLE demographics
(
    input varchar(100),
    name  varchar(100),
    age   int

)

-- Sample data
5*8*9
NULL
8
16
1*4
-- Sample row DemographicsLookup
colName 0   1  2       3 ...
------- -   -  -       -
sleep   Yes No Unknown

Thanks for the help

Answer 1

What with all the terrible string parsing going on, and the lack of normalization, it's hard to say exactly what you are trying to achieve here. You cannot do dynamic SQL in a function, but you could achieve something similar by unpivoting.

Your issue here essentially stems from the fact that you are storing denormalized data: you have multiple identical columns which should have been in separate rows, and you have *-separated input data which also should be in separate rows, with an ordinal column.

Had you done so then it would have been a simple join. As it is, it's necessary to split and unpivot things. It seems you could transform your function into an inline Table Valued Function like this:

• Get the row you want from DemographicsLookup.
• Unpivot the columns into separate rows.
• Use OPENJSON (and REPLACE) to get a ordinal list of values from @input. In Azure SQL you can use STRING_SPLIT and get an ordinal column. If you don't care about the order of the output then you can use STRING_SPLIT regardless.
• Join those together to get only the rows you want (those were the columns)
• Aggregate using STRING_AGG

CREATE OR ALTER FUNCTION dbo.fn_DemographicsLookup (
  @input VARCHAR(100),
  @colName VARCHAR(50)
)
RETURNS TABLE
AS RETURN

SELECT
  resultString = STRING_AGG(u.Val, '|') WITHIN GROUP (ORDER BY i.[key])
FROM (
    SELECT dl.*
    FROM DemographicsLookup dl
    WHERE dl.colName = @colName
) dl
UNPIVOT (
    Val FOR Col IN (
      [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16]
    )
) u
JOIN OPENJSON('[' + REPLACE(@input, '*', ',') + ']') i ON i.value = u.Col;

go

You would use it like this

SELECT dl.*
FROM demographics d
CROSS APPLY dbo.fn_DemographicsLookup(d.input, d.name) dl;

db<>fiddle