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.

This is a table-valued function which returns result a TABLE. It has to be used as a table like "Select * from ..." — p2k, Jan 30 '18 at 06:42

score 2 · Accepted Answer · answered Jan 30 '18 at 06:51

2

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

answered Jan 30 '18 at 06:51

lucky

12,734
4
24
46

Thanks, it works like a charm. I will mark this as an answer. – csamleong Jan 30 '18 at 10:04

score 0 · Answer 2 · answered Jan 30 '18 at 06:47

0

Here is your Answer

RETURNS @Items TABLE (
  Item                          VARCHAR(500)
)

In your code your are returning to a table so you have to use function something like this

Select * FROM dbo.Split('1,2,3,4,5,6',',')

Result

Item
1
2
3
4
5
6

answered Jan 30 '18 at 06:47

Imran Ali Khan

8,469
16
52
77

It is as mentioned, however, stormcloak gave an alternative to solve my problem, i will his as an answer. Nevertheless, thanks for your help! – csamleong Jan 30 '18 at 10:05

UDF Not found in SQL SELECT statement

2 Answers2