如何在 sql 中找到全名的匹配(顺序无关紧要) 对于

Question

例如："Swati Prakash Phapale"，则可能的条件是"prakash swati phapale"，"swati phapale prakash"，prakash swati phapale"或多重排列

Answer 1

DECLARE @fName varchar(max) =ltrim(rtrim(isnull('swati','')))
DECLARE @mName varchar(max) =ltrim(rtrim(isnull(' phapale','')))
DECLARE @lName varchar(max)  =ltrim(rtrim(isnull(' prakash','')))
DECLARE @FullName varchar(max), @Split char(1)=' ' ,@X1 xml,@i int=0
if len(@mName)!=0 
set @FullName = @fName +' ' + @mName +' '+@lName
else
begin
set @FullName = @fName +' '+@lName
end
IF len(@FullName)=0 BEGIN
SET @FullName = 'null' END DECLARE @hit AS varchar(MAX)='',
@EmptyGuid UniqueIdentifier='00000000-0000-0000-0000-000000000000'
SELECT @X1 = CONVERT(xml,' <root> <s>' + REPLACE(@FullName,@Split,'</s> <s>') + '</s>   </root> ')
declare @tmp1 as table(name varchar(max))
declare @tmp2 as table(RowNo bigint,name varchar(max))
insert into @tmp1(name)select * from (SELECT  name = T.c.value('.','varchar(max)')FROM @X1.nodes('/root/s') T(c)) T
declare @num as int;
set @num = (select count(*) from @tmp1);
-- Permutations of #t
with T (name, level) as(select convert(varchar(max), name), level=1 from @tmp1 union all select convert(varchar(max),t1.name+' '+T.name),level+1
from @tmp1 t1,T where level < @num and charindex(t1.name,T.name) = 0)
insert into @tmp2(RowNo,name)select RowNo = Row_Number() over (order by name), name from T where level = @num
SET @hit= isnull((select tc.name from @tmp2 t  inner join **YourTableName** tc on t.name=tc.name),'') 
SET @hit=concat('Full Name Matched with:',@hit)
IF len(@hit)>0 --@hit<>''
SET @hit=concat('FullName Matched with:',@hit)
Else
BEGIN
SET @hit=concat('No FullName Matched with:',@hit)
end
print @hit
Note : In 'YourTableName', 'Name' column contain fullname