First n words - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

First n words

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-10-27 : 13:49:51

Hi,

Is there a nice easy trick for getting the first 100 words from a text column?

I need to be able to express this for a view column.

Cheers, XF.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-27 : 14:27:14

If you've got a tally table handy:

SELECT SubString(textCol, 1, n)
FROM myTable, numbers
WHERE Len(SubString(textCol, 1, n))-Len(Replace(Substring(textCol, 1, n), ' ', ''))=100 AND SubString(textCol, n, 1)=' '
and n<8000

Warning: not tested. But if it works without modification, you owe me a beer.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-10-27 : 14:57:51

I couldn't make it work exactly as is, but with a slight modification I was able to make it work. The problem is that if the last character is a space, then the LEN command returns the length up to the character before the space. If you substitute DATALENGTH for the LEN function then you are up and cooking.

You probably still owe Rob a warm beer if nothing else as this was a cool solution.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-10-29 : 18:42:30

Thanks for your help.

I've used a UDF because I think the performance will be better and I have more control.

CREATE FUNCTION dbo.FirstNWords (@text varchar(8000), @wordCount int) 
RETURNS varchar(8000) 
AS 
BEGIN

DECLARE @i int
SET @i = 1;

DECLARE @wordsCounted int
SET @wordsCounted = 0

WHILE @i <= LEN(@text)
BEGIN
	IF SUBSTRING(@text, @i, 1) = ' '
	BEGIN

		SET @wordsCounted = @wordsCounted + 1

		IF @wordsCounted = @wordCount
		BEGIN
			
			SET @i = @i - 1
			BREAK;

		END

	END


	SET @i = @i + 1

END

RETURN SUBSTRING(@text, 1, @i)

END

I'm not entirely happy with it because two spaces in a row will be counted as two words. So there ought to be an inner loop but I'm not sure its worth it.

XF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources