Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

parse individual words from text field

Posted on 2005-04-05
2
Medium Priority
?
329 Views
Last Modified: 2008-03-17
Using only MSSQL I need to take the words in a text/varchar and pull each individual word out, check to see if it is in the "noise" list (a separate table) and ignore it if it is in the "noise" table, if it is not noise then put each word into another table with the ID field of the record the text/varchar field belongs to. So basically there would be a list of the non-noise words in a separate table with the ID of the original record where they resided in the text/varchar field. Basically creating a list of keywords that links back to the original record. I don't know the string fuctions in sql to be able to separate out the data into individual words and such.
0
Comment
Question by:Donzella
2 Comments
 

Author Comment

by:Donzella
ID: 13712546
Tinkering around I put this together. Is this something that looks effeicient enough? There are about one million records I need to run this on so efficiency would be important. This isn't doing the actual insert, just breaking up the strings into individual words.

Create table [TempTable] (TempField nvarchar(100))

Declare @EndWhen int,
        @TempPosition int,
        @TempString varchar(8000),
        @TempHoldString varchar(8000)
 
Set @EndWhen = 0
Set @TempString = 'this is a test'

If DataLength(@TempString) <= 1
Begin
  Set @EndWhen = 1
End

While @EndWhen != 1
Begin

  Set @TempPosition = Charindex(' ',@TempString)
  IF(@TempPosition=0)
  BEGIN
    Set @TempHoldString = @TempString
    Set @TempString = ''
  END
  ELSE
  BEGIN
    Set @TempHoldString = left(@TempString,@TempPosition)
    Set @TempString = Right(@TempString,DataLength(@TempString) - @TempPosition)
  END

  Insert Into [TempTable] (TempField)
  Values (@TempHoldString)

  If DataLength(@TempString) <= 1
     Begin
         Set @EndWhen = 1
     End

End


select * from [TempTable]

DROP TABLE [TempTable]
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 13721920
I think the code below will be considerably more efficient since it avoids assigning values to string variables, which is slow in SQL Server.


CREATE TABLE [TempTable] (TempField VARCHAR(100))

--**** setup/initialization (params for a sp or function, for example)

DECLARE @TempString VARCHAR(8000)

SET @TempString = '  this is a test string  '


--**** actual processing

DECLARE @WordStart INT
DECLARE @WordEnd INT
DECLARE @TempStringLen INT

--add a delim to end of string (last word)
--so don't have to check every time to see if it exists or not
SET @TempString = LTRIM(RTRIM(@TempString)) + ' '
SET @TempStringLen = LEN(@TempString)
SET @wordStart = 1

SET NOCOUNT ON
WHILE @wordStart <= @TempStringLen
BEGIN
      SET @wordEnd = CHARINDEX(' ', @TempString, @wordStart + 1)
      INSERT INTO [TempTable] (TempField)
            VALUES (SUBSTRING(@TempString, @WordStart, @WordEnd - @WordStart))
      SET @wordStart = @wordEnd + 1
END --WHILE
SET NOCOUNT OFF

SELECT TempField
FROM [TempTable]

DROP TABLE [TempTable]
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question