SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Help With Sql Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Storm
Starting Member

10 Posts

Posted - 01/21/2010 :  10:32:14  Show Profile  Reply with Quote
I need a sql statement that will separate words in a sentence.

I have this field in sql that has comments in them. I want to break up those comments into separate words.

I have roughly 80,000 lines of comments

Please Help

Thanks

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 01/21/2010 :  10:33:55  Show Profile  Visit webfred's Homepage  Reply with Quote
We need
table structure
example data
wanted output in relation to sample data


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Storm
Starting Member

10 Posts

Posted - 01/21/2010 :  10:58:32  Show Profile  Reply with Quote
The data type is set to Varchar(max)
and the column name is just 'answer'.

In the column you just have a sentence or comment. See 3 lines of examples.

1. The instructors were very professional and a great deal of vaulable information was taught.

2. Great rooms to stay in and it was very close to the training building

3. I learned more here at PEC than I did at MOSQ school. Strongly recommend these courses be mandatory refresher every 2 yrs.

I need a sql statement that will split these sentences up and display the words separately.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  11:04:44  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
jsut use a string splitting function. There are lots of different way.

Peruse this for a more or less complete discussion:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

THis is the function I use sometimes

IF EXISTS (
	SELECT * from dbo.sysobjects
	WHERE 
		id = object_id(N'[dbo].[fn_Split]')
		AND xtype IN (N'FN', N'IF', N'TF')
	)
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		position INT IDENTITY PRIMARY KEY
		, value VARCHAR(MAX)
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END
GO

It's not the best performing method I'm sure but it works.



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Storm
Starting Member

10 Posts

Posted - 01/21/2010 :  11:53:25  Show Profile  Reply with Quote
Using this function that returns a table you are limited to passing 1 row at a time because SQL can't return more than 1 table. I'm looking for a way to pass several records to make one list of words.

SELECT * FROM dbo.split4((SELECT ANSWER FROM SURVEY_ANSWERS WHERE (FK_QUESTION_ID IN (463, 464)) AND (ANSWER <> '')), ' ')

Error:
Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  12:11:51  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
you can use cross apply.

Example

DECLARE @foo TABLE (
	[Id] INT
	, [text] VARCHAR(255)
	)

INSERT @foo ([ID], [text])
      SELECT 1, 'asdas asdas asd rtwsdf fd'
UNION SELECT 2, 'sada asd asd a'

SELECT
	f.[ID]
	, f.[text]
	, f2.*
FROM
	@foo f

	CROSS APPLY dbo.fn_Split(f.[text], ' ')  f2


Results:

ID	text				position	value
1	asdas asdas asd rtwsdf fd	1		asdas
1	asdas asdas asd rtwsdf fd	2		asdas
1	asdas asdas asd rtwsdf fd	3		asd
1	asdas asdas asd rtwsdf fd	4		rtwsdf
1	asdas asdas asd rtwsdf fd	5		fd
2	sada asd asd a			1		sada
2	sada asd asd a			2		asd
2	sada asd asd a			3		asd
2	sada asd asd a			4		a

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 01/21/2010 12:17:25
Go to Top of Page

UKtheBUNNY
Starting Member

USA
1 Posts

Posted - 01/21/2010 :  14:40:19  Show Profile  Reply with Quote
I thank you more than you'll ever know.
Go to Top of Page

Storm
Starting Member

10 Posts

Posted - 01/21/2010 :  14:42:10  Show Profile  Reply with Quote
Same here it worked.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/22/2010 :  01:46:36  Show Profile  Reply with Quote
No offence to T.C. but if you are going to do this a lot there are set based functions for doing SPLIT that are much more efficient than T.C.'s loop based example. Won't make much difference if you are splitting up to, say, 20 items at a time, but will become noticeable when longer lists are split.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/22/2010 :  04:11:49  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Kristen is right. (of course).

I did post this link before my example:
quote:

jsut use a string splitting function. There are lots of different way.

Peruse this for a more or less complete discussion:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648


And you should read the articles there. there are lots of different ways and the way that I posted is pretty slow.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/22/2010 :  04:37:07  Show Profile  Reply with Quote
"the way that I posted is pretty slow"

Actually, I don't think it makes much difference for small numbers of splits/call (probably the case here).

However, once installed a Split function is likely to be used for all future splits , so a bit of research into what is the most efficient method is probably worthwhile.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000