split string

[andyc209]

i am looking to split a string into its individual words and then store these words in a table via a stored proc

my table is tbl_words with a column called words

if the string is 'i am stuck with sql' then i want the rows in the table to be each word in that sentence.

thanks for any help

 

[Olaf Doschke]

How about these solutions?

[URL unfurl="true"]http://www.codeproject.com/Articles/38843/An-Easy-But-Effective-Way-to-Split-a-String-using[/url]
[URL unfurl="true"]http://madprops.org/blog/splitting-text-into-words-in-sql-revisited/[/url]

If you take a close look you see I commented the second article and used the code to split text into sentences instead of sentences into words.

You might find it easier to make use of fulltext indexing and search capabilities of T-SQL, see:
[URL unfurl="true"]https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/[/url]

Bye, Olaf.

 

[johnherman]

Something like this:
1. locate the leftmost space in the string; sample command INSTRING or INSTR depending on your SQL dialect.
2. substring the first word by using the location of the space located in step 1. SUBSTRING or SUBSTR are the sample commands. Note that step 1 and 2 can be combined.
3. locate the next space and continue. this could be done by removing the first word from the string and repeating steps 1 and 2. and there are other ways.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gk53]

this is the code which will split words and put them into table

declare @string varchar(8000), @delimiter varchar(10)

select @string = 'You can proceed similarly to get the second one and so on by combining ', @delimiter = ' '
declare @tbl_words as table
(
	words varchar(5000)
)
declare  @separator varchar(12)
select @separator = '~' + @delimiter + '~'
declare @stringPart varchar(max);
set @stringPart = '';

select @string =rtrim(@string) + @delimiter
select @string =REPLACE(@string,@delimiter,@separator)



while charindex(@delimiter, @string) > 0
begin
    set @stringPart = substring(@string, 0, charindex(@separator, @string));
    insert into @tbl_words (words) values (@stringPart);
    set @string = substring(@string, charindex(@separator, @string) + len(@separator), len(@string) + 1);
end