a20213 (4/19/2013)
this is the Split function
FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) returns @Results TABLE (Items nvarchar(4000))as
begin
declare @index int
declare @slice nvarchar(4000)
select @index = 1
if @String is null return
while @index != 0
begin
select @index = charindex(@Delimiter,@String)
if @index !=0
select @slice = left(@String,@index - 1)
else
select @slice = @String
insert into @Results(Items) values(@slice)
select @String = right(@String,len(@String) - @index)
if len(@String) = 0 break
end
return
end
Take a look at the link in my signature about splitting strings. It will blow the doors off the while loop splitter for performance.
One challenge I see here is in your example I don't really understand what you are trying to do. You have full names in your @Names table and your have the same full name in @Searchs. Why do you want/need to split them to find the same value? I suspect that your example here is greatly simplified and it does not have quite enough details to help with your real situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/