How to retrieve 5 (or more than 1) words in SELECT

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)

How to retrieve 5 (or more than 1) words in SELECT

Author

Topic

alice
Starting Member

10 Posts

Posted - 2003-05-13 : 17:30:47

I want to retreive 5 (or more than 1) words (not characters) in the string from one of the column in the table.

I am puzzled how to write the SELECT statement in SPROC. Where and how to add the word parsing function inside the SELECT statement?

Thanks!

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-13 : 18:39:42

Even if they are words (which are group of chars), you could still use substring function as it counts spaces also.
I hope i understood your question.
ex:
select substring(COURSE_TITLE,1,15) from PS_TRAINING
--
where 1 = starting position and 15 is the # of chars to be displayed
-------
returns:
---------------
5060 Rim Clamp
Remove Assembly
RMA Recertifica
Bead Inspection

alice
Starting Member

10 Posts

Posted - 2003-05-13 : 22:00:30

quote:

Even if they are words (which are group of chars), you could still use substring function as it counts spaces also.
I hope i understood your question.
ex:
select substring(COURSE_TITLE,1,15) from PS_TRAINING
--
where 1 = starting position and 15 is the # of chars to be displayed
-------
returns:
---------------
5060 Rim Clamp
Remove Assembly
RMA Recertifica
Bead Inspection

My question is I only want to get the first 5 words, no matter how many chars. your solution only can get 15 chars, how to get the 5 words (maybe more 15 chars).

Thax

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-13 : 23:56:43

i'll do 2 words you figure out the rest.
you'll have to add provisions for strings with less words and strings with exactly n words.

tblx
key phrase
1 blah blah
2 blah blah x

select key,
substr(phrase,1,charindex(' ', phrase, 1)-1) As firstword,
substr(phrase,charindex(' ', phrase,1)+1,charindex(' ',phrase,charindex(' ',phrase, 1)+1)-1) As secondword

there is also two articles on the site that you can try as an approach. its on converting delimited rows to multiple rows and vice versa.

http://www.sqlteam.com/item.asp?ItemID=2652

once you have a table in such format you could filter by itemid

then combine by using the following technique.

http://www.sqlteam.com/item.asp?ItemID=11021

Edited by - ValterBorges on 05/14/2003 00:15:45

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-14 : 02:58:18

Sigh...here you go


USE NorthWind

-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'FindFifthWord')
	DROP FUNCTION FindFifthWord
GO

CREATE FUNCTION FindFifthWord 
	(@str varchar(1000))
RETURNS int
AS
BEGIN
	DECLARE @SpacesFound INT
	DECLARE @LastSpacePosition INT
	SET @SpacesFound = 0
	SET @LastSpacePosition = 0

	WHILE (@SpacesFound < 5)
	BEGIN
		IF (CHARINDEX(' ', @str, @LastSpacePosition + 1) = 0)
			BREAK
	
		ELSE
		BEGIN
			SET @LastSpacePosition = CHARINDEX(' ', @str, @LastSpacePosition + 1)
			SET @SpacesFound = @SpacesFound + 1
		END
		
	END

	RETURN @LastSpacePosition
END
GO

-- =============================================
-- Example to execute function
-- =============================================

SELECT 
LEFT('This is the longest darned statement i could type', dbo.FindFifthWord('This is the longest darned statement i could type') )

SELECT LEFT(cast(Notes as varchar), dbo.FindFifthWord(cast(Notes as varchar))) FROM Employees	
GO

alice
Starting Member

10 Posts

Posted - 2003-05-14 : 07:03:18

Great Code, Very helpful!!

Thank you!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-14 : 21:03:34

ah, yes udf's.

Good one mohdowais.

alice
Starting Member

10 Posts

Posted - 2003-05-14 : 22:03:46

One problem is that the SQL server does not support user-define function, so it must be replaced by sproc, the other problem is how to call this SPROC inside other SPROC correctly.

alice
Starting Member

10 Posts

Posted - 2003-05-14 : 22:58:57

My code is:

CREATE PROCEDURE FindFifthWord
(
@str varchar(1000),
@LastSpacePosition int output
)

AS
BEGIN
DECLARE @SpacesFound INT

SET @SpacesFound = 0
SET @LastSpacePosition = 0

WHILE (@SpacesFound < 5)
BEGIN
IF (CHARINDEX(' ', @str, @LastSpacePosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @LastSpacePosition = CHARINDEX(' ', @str, @LastSpacePosition + 1)
SET @SpacesFound = @SpacesFound + 1
END

END

END

But I always get error in following statement:

declare @startposition int
SELECT LEFT(Notes, EXEC FindFifthWord Notes, @LastSpacePosition=@startposition OUTPUT)
FROM Employees
GO

How to call this FindFifthWord SPROC

Thanks

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-16 : 11:52:34

Hi Alice:

You can not use a stored proc that way, and this is why we have UDFs

. Its rather unfortunate that you cant use a UDF (are you using SQL 7?)...any ideas, anyone?

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-16 : 12:12:19

Here's on way ....

declare @t table (v varchar(1000))

insert into @t
select 'this is the first word in the senetence' union all
select 'here is another sentence for you' union all
select 'and yet here is another one for you to enjoy' union
select 'less than five' union
select 'works, too'

select substring(v, 1, p1-1) as Word1,
substring(v, p1+1, p2-p1) as Word2,
substring(v, p2+1, p3-p2) as Word3,
substring(v, p3+1, p4-p3) as Word4,
substring(v, p4+1, charindex(' ',v,p4+1) - p4) as Word5
from
(
select v, p1, p2, p3, charindex(' ', v,p3+1) as p4 from
(
select v, p1, p2, charindex (' ',v,p2+1) as p3 from
(
select v, p1, charindex(' ',v,p1+1) as p2 from
(
select v + space(5) as v, charindex(' ', v + ' ') as p1 from @t
) a
) b
) c
) d

- Jeff

Edited by - jsmith8858 on 05/16/2003 12:13:19

alice
Starting Member

10 Posts

Posted - 2003-05-16 : 15:03:54

Thank you!!!!!!!!

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-16 : 15:57:44

Just for kick I offer this additional set-based solution.

CREATE TABLE #Temp(
String Varchar(50)
)

INSERT #TEMP Values('Here is one for all the Hairy Yaks.')
INSERT #TEMP Values('Todd is super smooth fantastic, Ace.')
INSERT #TEMP Values('Beer and Cheerios rock the Casba')

SELECT SUBSTRING(String, 1, Tally)
FROM #Temp
CROSS JOIN Tally
WHERE LEN(SUBSTRING(String,1,Tally)) -
LEN(REPLACE(SUBSTRING(String,1, Tally),' ','')) = 4 AND
SUBSTRING(String,Tally+1,1) = ' '

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-16 : 22:39:23

Nice CROSS JOIN Todd.

Lol. Jeff, i think he wants your title.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources