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...
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
This might eliminate the error (just ommit the [DBname]):
SELECT [dbo].[fncUtlSplit]([Split], ',','')
From [dbo].[TableA] 

Open in new window

However, a problem here is that you cannot SELECT [dbo].[fncUtlSplit]... fncUtlSplit is actually a TABLE (look at the return type from the function's definition).

You use that function as if it were a table. simple example with a hardocded value
select * from [fncUtlSplit]('1,2,3,4,5,6', ',', '')

Open in new window


Now... for your TableA, you want the function to be executed for each line. So you use OUTER APPLY.
with TableA as (
	select 1 id, '1,2,3,4,5' Split
	union select 2, 'a,b,c,d'
)
select TableA.*, split_result.SplitValue
from TableA
	outer apply [dbo].[fncUtlSplit](TableA.Split,',','') split_result

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
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...
0
MlandaTCommented:
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

0
Amour22015Author Commented:
Thanks for helping me, this is great....
0
MlandaTCommented:
Glad it worked :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.