I want my function to return a table that contains the numbers inside the #

Question

I am getting the following error :

Msg 208, Level 16, State 6, fnToken Procedure, Line 24
Invalid object name 'dbo.fnToken'.

I tried returning all the numbers ..

select dbo.fnToken('#254#251451#')
ALTER FUNCTION dbo.fnToken
(   
@Token varchar(100)
)
RETURNS @Listenum TABLE(num varchar(50))
AS
begin
declare @compt int
declare @num varchar(50)
set @compt = 1
while  SUBSTRING(@token,@compt,1)<>''
    begin
        if SUBSTRING(@Token,@compt+1,@compt)<>'#'
            begin
                set @num= @num+SUBSTRING(@Token,@compt+1,1)
                set @compt=@compt+1
            end
        else 
            begin
                Insert  into @Listenum(num) values(@num)
                
            end 
    end 
Return (SELECT num FROM @Listenum)

ENd

This is the expected output :

254

251451

Can't you just `REPLACE()` them? For the error just fix your `select dbo.fnToken('#254#251451#')` you don't specify what to select from `dbo.fnToken` — Ilyes, Jul 04 '19 at 08:25
i want to return the numbers inside that token with a table function — Younes Harir, Jul 04 '19 at 08:28
I am a Sql server 2008R2 user So i guess those functions wont work for me , — Younes Harir, Jul 04 '19 at 08:31
2008R2 has 5 days of support left, @YounesHarir, you should really look at updating. — Thom A, Jul 04 '19 at 08:32

score 1 · Answer 1 · answered Jul 04 '19 at 08:31

You can try:

SELECT  value
  FROM  STRING_SPLIT('#254#251451#', '#')
 WHERE  TRIM(value) <> '';

Depending on your version of SQL-Server its a built in function. If your on an older version have a look at string split functions on here. There are plenty of options.

score 0 · Answer 2 · answered Jul 04 '19 at 08:30

You can't reference a Table-Value Function like a Scalar Function. But this is a really bad way of splitting values out of a delimited value as it's iterative. Use an XML Splitter, DelimitedSplit8K_LEAD or STRING_SPLIT (if you're on SQL Server 2016+).

If you're using STRING_SPLIT the correct syntax would be:

SELECT SS.[value] AS num
FROM STRING_SPLIT(''#254#251451#','#') SS
WHERE SS.[value] != '';

The same syntax would be true for DelimitedSplit8K_LEAD, if you're not on SQL Server 2016+.

Edit: Op is using an old (and about to be completely unsupported) version of SQL Server, so they will need to use DelimitedSplit8k.

score 0 · Answer 3 · answered Jul 04 '19 at 09:33

Here is a solution. Hope to help, my friend :))

I added more @separator param, you can remove it by fixing "#" value.

ALTER FUNCTION dbo.fnToken
(   
@stringToSplit VARCHAR(MAX), @separator nchar(1)
)
RETURNS @Listenum TABLE(num varchar(50))
AS
begin
DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(@separator, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@separator, @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @Listenum 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @Listenum
 SELECT @stringToSplit
Return

ENd 


SELECT num 
FROM dbo.fnToken('#254#251451#', '#')
WHERE TRIM(num) <>''

I want my function to return a table that contains the numbers inside the #

3 Answers3