Get Words from String SQL Server 2012

Get Words from String SQL Server 2012

Getting Words from a String/column taking space as a delimiter. Here is the Script.

It can be done 2 ways. In this example used CharIndex and SubString functions,CharIndex returns

character position in the String,SubString returns subString of the string ,you need to specify starting location and length/portion of the String.

Method1: Getting Words from a String with changing positions

declare @sentence varchar(250)=N'hello this is my world';
declare @sidx int=1,@lidx int=len(@sentence);
declare @len int = len(@sentence);


while @lidx != 0
begin


--find first space char
set @lidx=CHARINDEX(' ',@sentence,@sidx);




if(@lidx <>0)
print 'word:'+ltrim(rtrim(substring(@sentence,@sidx,@lidx-@sidx)));
else print 'word:'+ltrim(rtrim(substring(@sentence,@sidx,@len-@lidx)));

--change starting loc
set @sidx = @lidx+1;

--change ending index
if(@lidx <> 0)
set @lidx =@len;

end

Output:

word:hello
word:this
word:is
word:my
word:world

Method 2:Getting Words from a String with changing Source 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=charindex(' ',@sentence,@sidx);

             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 String SQL Server 2012,using charindex String function,using SubString String function,using ltrim String function in T-SQL.