asked on

TSQL - Function for split

Hi Experts,

I need some help with putting this together I have this function:

ALTER FUNCTION [dbo].[fncUtlSplit] 
(
@str_in nvarchar(max),
@separator nvarchar(1),
@delimiter nvarchar(2) 
)
RETURNS @SplitValues TABLE (SplitValue NVARCHAR(max))
AS
BEGIN

DECLARE	@Occurrences integer,
		@Counter integer,
		@tmpStr nvarchar(max),
		@ToParse nvarchar(max)

IF LEN(@delimiter) > 0 			
	SET @ToParse = SUBSTRING(@str_in, 1, charindex(@delimiter, @str_in, 1))
ELSE
	SET @ToParse = @str_in

SET @Counter = 0

IF SUBSTRING(@ToParse,LEN(@ToParse),1) <> @separator
	SET @ToParse = @ToParse + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@ToParse,@separator,@separator+'#')) - DATALENGTH(@ToParse))/ DATALENGTH(@separator)
SET @tmpStr = @ToParse

WHILE @Counter <= @Occurrences
	BEGIN
		SET @Counter = @Counter + 1
		INSERT INTO @SplitValues
			VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

		SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,LEN(@tmpStr))

		IF DATALENGTH(@tmpStr) = 0
			BREAK
	END
RETURN
END

Open in new window

What I think it does is it splits a value in a column. So in my column from another table I have:

TableA.Split

Column Split has:

1,2,20,17,19,15

I think the function will split for me and I tried by doing:

SELECT
[dbname].[dbo].[fncUtlSplit]([Split], ',','
')
From  [DBName].[dbo].[TableA]

Open in new window

But I am getting errors:
sg 4121, Level 16, State 1, Line 1
Cannot find either column "DBName" or the user-defined function or aggregate "dbname.dbo.fncUtlSplit", or the name is ambiguous.

Thanks for helping me...

ASKER CERTIFIED SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Ok,

I have this:

SELECT vw.[txtID]
      ,vw.[ConsultantServices]
	  ,split_result.SplitValue
  FROM [noah2-siop].dbo.tblNAsContacts SIOP Join [SIOP_siophal].[dbo].[vwConsultants] VW
  On SIOP.Contact = VW.txtID Join 
  (
  select TXTID, [ConsultantServices], split_result.SplitValue
from [SIOP_siophal].[dbo].[vwConsultants]
	outer apply [noah2-siop].[dbo].[fncUtlSplit]([SIOP_siophal].[dbo].[vwConsultants].[ConsultantServices],',','') split_result
On vw.txtid = split_result.txtid
)
  Where vw.ConsultantServices Is Not Null And vw.ConsultantServices <> ''

Open in new window

but am still getting errors on:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'On'.

Note:
tableA = [vwConsultants]
And
Split = [ConsultantServices]

I was just using tableA.Split for the names before, but to make less confusing I put in the correct names.

I don't think I need:

with TableA as (
	select 1 id, '1,2,3,4,5' Split
	union select 2, 'a,b,c,d'
)

Open in new window

once the splitting is done I am going to have to join another table to get that value from the description of the numbers 1 - 20.

Please help and thanks...

Mlanda T

try that

SELECT 
	vw.[txtID], 
	vw.[ConsultantServices],
	split_result.SplitValue
FROM [noah2-siop].dbo.tblNAsContacts SIOP 
	Join [SIOP_siophal].[dbo].[vwConsultants] VW On SIOP.Contact = VW.txtID 
	outer apply [noah2-siop].[dbo].[fncUtlSplit](VW.[ConsultantServices],',','') split_result
Where vw.ConsultantServices Is Not Null And vw.ConsultantServices <> ''

Open in new window

Amour22015

ASKER

Thanks for helping me, this is great....

Mlanda T

Glad it worked :)