How to extract the word in a string using sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to extract the word in a string using sql 2005

Author

Topic

ryoka012
Starting Member

20 Posts

Posted - 2013-08-29 : 05:07:53

Hi,

Can any help me with on creating a stored procedure in extracting a word in a sentence.

Example:
String=The \ quick \ brown \ fox \ JuMp \ over \ the \ lazy \ dog.

I want to extract a random word on the string.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-29 : 06:15:10

[code]-- User supplied parameter
DECLARE @Sample VARCHAR(100) = 'The\quick\brown\fox\Jump\over\the\lazy\dog';

-- Local helper variables
DECLARE @WordCount INT = 1 + LEN(@Sample) - LEN(REPLACE(@Sample, '\', '')),
@Items INT = 100,
@Word INT;

-- SwePeso
WHILE @Items > 0
BEGIN
SET @Word = 1 + ABS(CHECKSUM(NEWID())) % @WordCount;

SELECT @Word AS Part,
Data.query('/w[sql:variable("@Word")]').value('.', 'VARCHAR(100)') AS Word
FROM (
SELECT CAST('<w>' + REPLACE(@Sample, '\', '</w><w>') + '</w>' AS XML)
) AS f(Data)

SET @Items -= 1;
END[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources