UDF Not found in SQL SELECT statement

Question

I cannot wrap my head around on the select UDF statement referred from the link below:

How to split a comma-separated value to columns

CREATE FUNCTION dbo.Split (
  @InputString                  VARCHAR(500),
  @Delimiter                    VARCHAR(1)
)

RETURNS @Items TABLE (
  Item                          VARCHAR(500)
)

AS
BEGIN
  IF @Delimiter = ' '
  BEGIN
        SET @Delimiter = ','
        SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
  END

  IF (@Delimiter IS NULL OR @Delimiter = '')
        SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

  DECLARE @Item           VARCHAR(500)
  DECLARE @ItemList       VARCHAR(500)
  DECLARE @DelimIndex     INT

  SET @ItemList = @InputString
  SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
  WHILE (@DelimIndex != 0)
  BEGIN
        SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
        INSERT INTO @Items 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
  BEGIN
        SET @Item = @ItemList
        INSERT INTO @Items VALUES (@Item)
  END

  -- No delimiters were encountered in @InputString, so just return @InputString
  ELSE INSERT INTO @Items VALUES (@InputString)

  RETURN

END -- End Function
GO

I checked the permission and also made sure that the function has been created in the correct schema using:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

However, I got into problem when I ran the following:

select dbo.Split(TargetFolderId, ',') from ReportConfig where ReportId = 9

Error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous.

but I can do

select * from dbo.Split('stringA,stringB', ',')

Could it be the difference between the SSMS version that both of us are using?

I am using SQL2017 - SSMS v17.4.

Answer 1

Obviously, the function is defined as table-valued and you can't use it like that

select dbo.Split(TargetFolderId, ',') from ReportConfig where ReportId = 9

But you could perform CROSS APPLY to get TargetFolderId splitted values.

select sp.Item from ReportConfig rc
CROSS APPLY dbo.Split(rc.TargetFolderId) AS sp
where rc.ReportId = 9