• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 62
  • Last Modified:

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
0
Bharat Guru
Asked:
Bharat Guru
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now