Get words from a String using PatIndex in SQL SERVER

Get words from a String using PatIndex in SQL SERVER

This tutorial gets words from a String/text column, Splits a string using space as a delimiter.  

Using PatIndex string function which returns delimiter's first position. then remove each word from the string.

declare @sentence varchar(250)=N'hello this is my world';

declare @sidx int = 1, @len int= len(@sentence);
declare @lidx int = @len;

--Getting words from a String Changing Original Text

while @sentence is not null
begin

             set @lidx=patindex('%['' '']%',@sentence);

             if(@lidx<>0)

             print 'Word:'+substring(@sentence,@sidx,@lidx);
             else print 'Word:'+substring(@sentence,@sidx,@len);

             set @sentence = ltrim(substring(@sentence,@lidx,@len-@lidx+1));
             set @len= len(@sentence);
             if(@lidx =0) Set @sentence = NULL;
end;

OUTPUT

Word:hello
Word:this
Word:is
Word:my
Word:world


Tags: Get words from a String using T-SQL ,
Getting words from a String using T-SQL ,Splitting a string into words in sql-server, using PatIndex in sql server.