Mad Props!

How about this:

create function [dbo].[wordsplit](@str nvarchar(max),@delims nvarchar(max) = ' ,-:/')

returns @ResTable table  

(word nvarchar(max))  

AS  

begin  

insert into @ResTable

select val from (select

left(s,patindex(N'%['+@delims+']%',s+N',')-1) as val

from (

select num, substring(@str,num,len(@str)) as s

from dbo.nums

where num <= len(@str)+1

and charindex(substring(N','+@str,num,1),@delims) > 0

) splits) v

where val not in ('an', 'the', 'of', 'and', '&')

;

return

end

;

--then try select * from dbo.wordsplit('this is the test',default)

--NB: this requires an auxiliary table of numbers, check out http://robfarley.blogspot.com/2006/09/auxiliary-table-of-numbers.html for details of that. You will want to make sure that it's topped up to longer than your longest string before calling wordsplit.

To fix it for multiple characters, just add the empty string to the ones you remove at the end. :)

And yes, yours is faster when used as a function like this. But can you convert yours into a set-based approach?

For example, try running this:

declare @delims nvarchar(max);

set @delims = N' ,:/';

declare @start datetime;

set @start = current_timestamp;

select w.value

from AdventureWorks.Production.ProductDescription d

cross apply

dbo.splitwords(d.description) w

;

select 'Matt' as who, datediff(ms,@start, current_timestamp);

set @start = current_timestamp;

select w.word

from AdventureWorks.Production.ProductDescription d

cross apply

dbo.wordsplit(d.description,@delims) w

;

select 'Rob-UDF' as who, datediff(ms,@start, current_timestamp);

set @start = current_timestamp;

select left(s,patindex(N'%['+@delims+']%',s+N',')-1) as val

from (select num, substring(d.Description,num,len(d.Description)) as s

from AdventureWorks.Production.ProductDescription d

join

dbo.nums

on num <= len(d.Description)+1

and charindex(substring(N','+d.Description,num,1),@delims) > 0

) splits;

select 'Rob-set' as who, datediff(ms,@start, current_timestamp);

--You may find that the set-based approach works a little faster.

Great !!!!!!!!!

Thanks Matt! Here is my wordy commented version of your routine to help myself better understand it:

CREATE FUNCTION SplitWords(@text varchar(max))

RETURNS @words TABLE (

position smallint,

word varchar(max)

)

AS

BEGIN

-- Use like this:

-- SELECT * FROM dbo.SplitWords('This is a test')

-- Or this:

-- SELECT *

-- FROM dbo.SplitWords(@userinput) w

-- JOIN table1 t ON dbo.SplitWords(t.column1) = w.word

-- Or this:

-- SELECT *

-- FROM MyTable t

-- CROSS APPLY dbo.SplitWords(t.MyColumn) w ON w.word = @word

-- WHERE dbo

-- (Where @word is the word you're searching for.)

-- from: www.madprops.org/.../Splitting-Text-

-- see also: www.madprops.org/.../T-SQL-to-Split-

SET @text = RTRIM(@text)

DECLARE @delims varchar(10)

SET @delims = ' ,:/-'

DECLARE @pos smallint, @thisDelimPos smallint, @thisWord varchar(max)

SET @pos = 1

-- Get the position of the first non-delimiter character.

-- Push the current position forward by one,

-- as long as there is a delimiter beyond the current position.

WHILE @pos < LEN(@text) AND CHARINDEX(SUBSTRING(@text, @pos, 1), @delims) > 0

SET @pos = @pos + 1

WHILE @pos <= LEN(@text)

BEGIN

-- find the position of the next delimeter

SET @thisDelimPos = PATINDEX('%[' + @delims + ']%', SUBSTRING(@text, @pos, len(@text) - @pos + 1))

IF @thisDelimPos > 0

BEGIN

SET @thisDelimPos = @thisDelimPos + @pos - 1

IF @thisDelimPos > @pos

BEGIN

-- @thisDelimPos now holds the position of the earliest delimiter in the string

SET @thisWord = SUBSTRING(@text, @pos, @thisDelimPos - @pos)

INSERT INTO @words VALUES (@pos, @thisWord)

END

SET @pos = @thisDelimPos + 1

-- Again, push the current position forward by one,

-- as long as there is a delimiter beyond the current position.

WHILE @pos < LEN(@text) AND CHARINDEX(SUBSTRING(@text, @pos, 1), @delims) > 0

SET @pos = @pos + 1

END

ELSE

BEGIN

SET @thisWord = SUBSTRING(@text, @pos, LEN(@text) - @pos + 1)

INSERT INTO @words VALUES (@pos, @thisWord)

SET @pos = LEN(@text) + 1

END

END

-- remove common words that we don't want to search for

DELETE FROM @words

WHERE word IN ('an', 'the', 'of', 'and', '&')

RETURN

END

Hi matt.I used the last example and its commendable.How can I use this to compare between the values produced and table values i.e see if a string contains any of these words.

removing punctuation? other non-printable word delimiters (tab, newline, etc)? Did you test this on more than just "mary had a little lamb"? weak...

Great work! Thanks.

Thanks for that. It works well.

Great work! Thanks.

I've been doing this in much more complicated way. :) I was really astonished. :) But I have some problems. I have added new delimiters which are not working well.

I have added '-', because I have some calculations inside the text. If minus is declared as standalone delimiter it works, but if it is in combination with others it doesn't.

For example if I have: 'A-B-C', I get:

A-B-C

I really don't find the reason for this, because if I declare just minus as delims it works, I'm getting 3 rows.

Almost same thing was with '*' delimiter, I had to move it from the last to the first position in when declaring. I tried with minus too, no luck.

Did you have such problems?

Hi Matt,

Thank you. Now it works. :) I saw you have '-', but I have added some delimiters after it, so it didn't work. It was confusing when it was working with a space before '-' (a - b) and without not (a-b). :)

Thanks again.

Gud day! I have tried to split word and saved it in a temporary table. What I want to do is to make the first letter of each Item in a column Capitalized and combine the words again. But i'm having problem in combining the values.

Ex. table @container

-----------------------|

col1 | col2 |

-----------------------|

1 | De |

2 | Guzman |

-----------------------|

I want to have an output like:

'De Guzman'

I have tried to use this:

declare @output varchar(100) = ''

set @output = @output + ' ' + (Select col2 from @container where col1 = @counter)

Hope you can help me. Thanks in advance.