I am having two strings as @CountryLocationIDs and @LocationIDs with values:
@CountryLocationIDs = 400,600,150,850,160,250
@LocationIDs1 = 600,150,900
Then I need the output in another variable as:
@LocationIDs = 400,600,150,850,160,250,900
Anybody please help out... Thanks in advance...
I have created table-valued function which accepts two parameters, first is string with IDs, and second is delimiter in string.
CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
returns @temptable TABLE (items nvarchar(4000))
as
begin
declare @idx int
declare @slice nvarchar(4000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
After creating function, just use UNION set operator on this way:
EDITED
WITH ListCTE AS
(
select items from dbo.split('400,600,150,850,160,250', ',')
union
select items from dbo.split('600,150,900', ',')
)
SELECT TOP 1
MemberList = substring((SELECT ( ', ' + items )
FROM ListCTE t2
ORDER BY
items
FOR XML PATH( '' )
), 3, 1000 )FROM ListCTE t1
With UNION you will automatically get distinct values from both strings, so you don't need to use DISTINCT clause
Also you can use option with dynamic management function sys.dm_fts_parser
Before script execution you need check full-text component is installed:
SELECT FULLTEXTSERVICEPROPERTY ('IsFulltextInstalled')
0 = Full-text is not installed. 1 = Full-text is installed. NULL = Invalid input, or error.
If 0 = Full-text is not installed then this post is necessary to you How to install fulltext on sql server 2008?
DECLARE @CountryLocationIDs nvarchar(100) = '400,600,150,850,160,250',
@LocationIDs1 nvarchar(100) = '600,150,900',
@LocationIDs nvarchar(100) = N''
SELECT @LocationIDs += display_term + ','
FROM sys.dm_fts_parser('"'+ 'nn,' + @CountryLocationIDs + ',' + @LocationIDs1 + '"', 1033, NULL, 0)
WHERE display_term NOT LIKE 'nn%'
GROUP BY display_term
SELECT LEFT(@LocationIDs, LEN(@LocationIDs) - 1)
