how can we split and join the table

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,'[',''),',','')

Select allOpen in new window

It's just an idea. Please try to replicate on your end.
fnsplit.zip

If you're using SQL Server 2016 or later you can utilize the newly added STRING_SPLIT() function instead of writing your own.

getting error message
The multi-part identifier "t1.value" could not be bound.

you might have to encapsulate the value in brackets eg:

t1.[value] as "value" is a reserved word in SQL.

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

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

Select allOpen in new window

Is above function will work for 'This is a test [AB, BC, BD] test [EF] test1 [BD]?

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

Select allOpen in new window

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)

Thanks for help