How to get words from field in SQL Server 2008

Question

I need to get the words in a text field and make some updates with those words, for example:

original data

words        | other field    | another field
---------------------------------------------
white        |                |
some words   |                |
some other w |                |

desired result

words        | other field    | another field
---------------------------------------------
white        |                |
some         | words          |
some         | other          | w

How can I get this done?

EXTRA

I have this query where I get how many words a field have

select nombre, 
       LEN(words) - LEN(REPLACE(words, ' ', ''))+1 as palabras
  from origen_informacion
 where words <> ''

Answer 1

If you are trying to split a space separated string you can use this fucntion:

create function fn_ParseCSVString
(
@CSVString  varchar(8000) ,
@Delimiter  varchar(10)
)
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.fn_ParseCSVString ('qwe rew wer', ',c,')
*/
begin
declare @i int ,
    @j int
    select  @i = 1
    while @i <= len(@CSVString)
    begin
        select  @j = charindex(@Delimiter, @CSVString, @i)
        if @j = 0
        begin
            select  @j = len(@CSVString) + 1
        end
        insert  @tbl select substring(@CSVString, @i, @j - @i)
        select  @i = @j + len(@Delimiter)
    end
    return
end


GO

And pass ' ' as your delimiter.

select * from dbo.fn_ParseCSVString ('qwe rew wer', ' ')