Seva Seva - 2 months ago 16
SQL Question

Split function in T-SQL

I have table with field that contains coma separated string. I whant create query thats return follow result:

I want to create query that return split text. For exapmle:

Data in table:

| ID | Names |
| ------------------------------------ | -------------------------------- |
| 63F5D993-3AC9-4EEA-8007-B669542BAD9A | John Smith,Kerry King,Tom Arraya |


Required result:

ID | Names
------------------------------------ | -----------
63F5D993-3AC9-4EEA-8007-B669542BAD9A | John Smith
------------------------------------- | -----------
63F5D993-3AC9-4EEA-8007-B669542BAD9A | Kerry King
------------------------------------- | -----------
63F5D993-3AC9-4EEA-8007-B669542BAD9A | Tom Arraya


I found "split" function for T-SQL but its works not quite right for my case. I can't execute it like this:

SELECT dbo.Split(dbo.name, ',') FROM dbo.Mytable


It can execute only follows:

SELECT * FROM dbo.Split('John Smith,Kerry King,Tom Arraya', ',')


But it does not suit me.
Also i attempted write cursor:

DECLARE @bkb varchar(256)
DECLARE @Bkb_Cursor CURSOR
SET @Bkb_Cursor = CURSOR SCROLL FOR
SELECT bkb.best_know_by
FROM [sugarcrm_cmsru_dev].[dbo].[contacts] c
left JOIN [dbo].[email_addr_bean_rel] eb
ON eb.[bean_id] = c.[id]
JOIN [dbo].[email_addresses] ea
ON ea.[id] = eb.[email_address_id]
JOIN [dbo].[contacts_cstm] ccs
ON eb.bean_id = ccs.id_c
left JOIN [dbo].[BestKnowBy$] bkb
ON c.[campaign_id] =bkb.Con_id
where c.deleted = 0;
OPEN @Bkb_Cursor;
FETCH NEXT FROM @Bkb_Cursor
INTO @bkb;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT dbo.Splitfn(@bkb);
FETCH NEXT FROM @Bkb_Cursor INTO @bkb;
END
CLOSE @Bkb_Cursor;
DEALLOCATE @Bkb_Cursor;
GO


But it is not worked. I get error "Column "dbo" is not allowed in this context, and the user-defined function or aggregate "dbo.Splitfn" could not be found."

How can I solve this problem?

My query looks like follow:

SELECT c.[id],
bkb.best_know_by
FROM [sugarcrm_cmsru_dev].[dbo].[contacts] c
left JOIN [dbo].[email_addr_bean_rel] eb
ON eb.[bean_id] = c.[id]
JOIN [dbo].[email_addresses] ea
ON ea.[id] = eb.[email_address_id]
JOIN [dbo].[contacts_cstm] ccs
ON eb.bean_id = ccs.id_c
left JOIN [dbo].[BestKnowBy$] bkb
ON c.[campaign_id] =bkb.Con_id
where c.deleted = 0;


The bkb.best_know_by field contains comma separated string. How can i use "Cross Apply" in this case?

Answer

Cross Apply will do the trick

Select A.ID
      ,Names = B.Item   -- << Return Field from your Split Function
 From  YourTable A
 Cross Apply (Select * from dbo.Split(A.Names, ',') ) B

With your query

SELECT c.[id]
      ,S.*      --<< Removed bkb.best_know_by and Replaced with S.* (don't know your Split() Return Field)
 FROM  [sugarcrm_cmsru_dev].[dbo].[contacts] c
 LEFT JOIN [dbo].[email_addr_bean_rel]  eb ON eb.[bean_id] = c.[id]
 JOIN [dbo].[email_addresses] ea ON ea.[id] = eb.[email_address_id]
 JOIN [dbo].[contacts_cstm] ccs   ON eb.bean_id = ccs.id_c
 LEFT JOIN [dbo].[BestKnowBy$] bkb   ON c.[campaign_id] =bkb.Con_id
 Cross Apply dbo.Split(bkb.best_know_by,',')  S
 where c.deleted = 0;