how can we split and join the table

How to split by comma and join each value from table1 value field with table2 symbol

Table1(ID, Value)
1,   [AB, BC, DF, TD]
2,   [BC, AC, TD]


Table2(ID, Symbol)
1, AB
2, AC
3, BC
4, DF
5, TD
Bharat GuruAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
You need to write function which will split your values using some separator. This function will give you values as row.

This will be output for table1 and make inner join on Table2 but remember to remove special character like '",","[","]" etc.

Your final query would be like,

SELECT * FROM table1  t1
INNER JOIN dbo.fnSplit(t1.value,',') t2
ON 1=1
INNER JOIN Table2 t3 ON REPLACE(t3.Symbol,',','')=replace(replace(t2.val,'[',''),',','')

Open in new window


It's just an idea. Please try to replicate on your end.
fnsplit.zip
0
Kyle AbrahamsSenior .Net DeveloperCommented:
setup data:
select 1 id , 'AB, BC, DF, TD' vals into #t1
insert into #t1 select 2, 'BC, AC, TD'

Open in new window


Real Query:
select ROW_NUMBER() over (order by item) as ID, item as Symbol
from(
select distinct item  from 
#t1 t
cross apply dbo.fn_txt_Split(t.Vals,',')
) x

Open in new window


code for fn_txt_split:
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 09/14/2016 14:19:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 09/14/2016 14:19:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

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
Brian CroweDatabase AdministratorCommented:
If you're using SQL Server 2016 or later you can utilize the newly added STRING_SPLIT() function instead of writing your own.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bharat GuruAuthor Commented:
getting error message
The multi-part identifier "t1.value" could not be bound.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
you might have to encapsulate the value in brackets eg:

t1.[value] as "value" is a reserved word in SQL.
0
Bharat GuruAuthor Commented:
How can we split values only between open and close brackets [ ].   For example "This is a test [AB, BC, BD] test [EF, BD]"

AB
BC
BD
EF
BD
0
Brian CroweDatabase AdministratorCommented:
You're going to have to write your own table-valued string-parsing function to extract the comma-delimited list then pass it to the string_split function.

Here is some code that might help...

DECLARE @Test VARCHAR(MAX) = 'This is a test [AB, BC, BD] test [EF, BD]'

DECLARE @List TABLE (Item VARCHAR(MAX));

WITH cte AS
(
	SELECT @Test AS String,
		CHARINDEX('[', @Test, 1) AS StartIndex,
		CHARINDEX(']', @Test, 1) AS EndIndex,
		SUBSTRING(@Test, CHARINDEX('[', @Test, 1) + 1, CHARINDEX(']', @Test, 1) - CHARINDEX('[', @Test, 1) - 1) AS List,
		SUBSTRING(@Test, CHARINDEX(']', @Test, 1) + 1, LEN(@Test)) AS Remaining
	UNION ALL
	SELECT Remaining AS String,
		CHARINDEX('[', Remaining, 1) AS StartIndex,
		CHARINDEX(']', Remaining, 1) AS EndIndex,
		SUBSTRING(Remaining, CHARINDEX('[', Remaining, 1) + 1, CHARINDEX(']', Remaining, 1) - CHARINDEX('[', Remaining, 1) - 1) AS List,
		SUBSTRING(Remaining, CHARINDEX(']', Remaining, 1) + 1, LEN(Remaining)) AS Remaining
	FROM cte
	WHERE CHARINDEX('[', Remaining, 1) > 0
)
SELECT RTRIM(LTRIM(S.[value])) AS item
FROM cte
CROSS APPLY STRING_SPLIT(cte.List, ',') AS S

Open in new window

0
Bharat GuruAuthor Commented:
Is above function will work for 'This is a test [AB, BC, BD] test [EF] test1 [BD]?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
If you have an exe you don't need an installer.  You can simply run the sc command:

sc create MyService binPath= "C:\Code\MyService\MyService.Exe"  DisplayName= "My cool Service"  start= auto

Open in new window

0
Bharat GuruAuthor Commented:
Above code will not work when we don't open and close bracket
Select SUBSTRING('test', CHARINDEX('[', 'test', 1) + 1, CHARINDEX(']', 'test', 1) - CHARINDEX('[', 'test', 1) - 1)
0
Bharat GuruAuthor Commented:
Thanks for help
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
SQL

From novice to tech pro — start learning today.