sql server - check if first and last character of each word in a string is same -

i need set of names 1st & last letter of each part of name(first name, middle name, last name) matches. name can have 5 parts. if names are:

croc lil brob tart cron lee bro tasr 

it should result:

croc lil brob tart  declare @name varchar(70) = 'cron lee bro tasr test'     select @name, substring(@name,1,1) "first letter of first name",     substring(@name,charindex(' ',@name) +1 -2,1) "last letter of first name",     substring(@name,charindex(' ',@name) +1 ,1) "first letter of second name",     substring(@name,charindex(' ',@name, charindex(' ',@name) + 1) +1 -2 ,1) "last letter of second name",     substring(@name,charindex(' ',@name, charindex(' ',@name) + 1) +1 ,1) "first letter of third name",     substring(@name,charindex(' ',@name, charindex(' ',@name,charindex(' ',@name) + 1) + 1 +1)+1 -2 ,1) "last letter of 3rd name",     substring(@name,charindex(' ',@name, charindex(' ',@name,charindex(' ',@name) + 1) + 1 +1)+1 ,1) "first letter of 4th name",     substring(@name,charindex(' ',@name,charindex(' ',@name, charindex(' ',@name,charindex(' ',@name) + 1) + 1 +1)+1)+1 -2 ,1) "last letter of 4th name",     substring(@name,charindex(' ',@name,charindex(' ',@name, charindex(' ',@name,charindex(' ',@name) + 1) + 1 +1)+1)+1 ,1) "first letter of 5th name",     substring(reverse(@name),1,1) "last letter of 5th name"      substring(right(@name, charindex('.', reverse('.' + @name)) - 1),1,1) = substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) +1 -2,1) or      substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) +1 ,1) = substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1), charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) + 1) +1 -2 ,1) or      substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1), charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) + 1) +1 ,1) = substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1), charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) + 1) + 1 +1)+1 -2 ,1) or     substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1), charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) + 1) + 1 +1)+1 ,1) =      substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1), charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) + 1) + 1 +1)+1)+1 -2 ,1) or     substring(right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1), charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1),charindex(' ',right(@name, charindex('.', reverse('.' + @name)) - 1)) + 1) + 1 +1)+1)+1 ,1) = substring(reverse(right(@name, charindex('.', reverse('.' + @name)) - 1)),1,1) 

by select query able first & last character of each part of name. but, confused clause.

you create split-function, it's relatively easy:

select * dbo.tablename t not exists (    select 1  dbo.split(t.col, ' ')x    left(x.item, 1) <> right(x.item, 1) ) 

demo

here's 1 use:

create function [dbo].[split] (     @itemlist nvarchar(max),      @delimiter char(1) ) returns @itemtable table (item varchar(250))          begin         declare @tempitemlist nvarchar(max)     set @tempitemlist = @itemlist      declare @i int         declare @item nvarchar(4000)      set @i = charindex(@delimiter, @tempitemlist)      while (len(@tempitemlist) > 0)     begin         if @i = 0             set @item = @tempitemlist         else             set @item = left(@tempitemlist, @i - 1)         insert @itemtable(item) values(@item)         if @i = 0             set @tempitemlist = ''         else             set @tempitemlist = right(@tempitemlist, len(@tempitemlist) - @i)         set @i = charindex(@delimiter, @tempitemlist)     end      return end