Comparing numbers in two lines in sql server
I created a table-valued function that takes two parameters, first a string with ids, and second a delimiter in the 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 the function, just use UNION
the set statement like this:
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 different values from both strings, so you don't need to use the clauseDISTINCT
source to share
Also you can use the option with the dynamic control function sys.dm_fts_parser
Before executing the script, you need to check if the full-text component is installed:
SELECT FULLTEXTSERVICEPROPERTY ('IsFulltextInstalled')
0 = Full text not installed. 1 = Full-text is set. NULL = invalid input or error.
If 0 = Full-text is not installed, you need this message How do I install full-text 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)
source to share