text manipulation - 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)

text manipulation

Author

Topic

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-17 : 14:53:13

hey, hey
look what i've stumbled upon.

what a great site you guys have :)

Here's a brain teaser:
I have a table with a text column called say "text".
How do i create another column within the same table, that searches through the "text" column and retreives only distinct words.

For example, row 1 would be:
hi hi i am doctor seusse doctor seusse hi bye bye
becomes:
hi i am doctor seusse bye

I'm having a hard time with this problem. I think i've been approaching it the wrong way.

Can someone help me out here? (pointers, hints, tips..etc much appreciated!!)
PS: i have like over 1000 records, so i'm trying to develop a 'cursor like' method, so that the entire column can be revised at once.

cheers

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-17 : 15:08:14

Do you want to only remove adjacent duplicates or get truely distinct words? Example:

"I'm bitter. Not quite bitter like an orange-rind, but rather bitter like the scent in the air left by a round, discharged into the head of the unrighteous..."

Remove all but the first "bitter", "like" and "the"? or don't change anything?

[edit] oh and are you on sql 2k? if so there would be a user-defined function solution that we could cook up [/edit]

<O>

Edited by - Page47 on 07/17/2002 15:09:36

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-17 : 15:36:28

hey, thanks for the quick reply man

yes, i am on sql 2k
I'd like to remove ALL but the first of any unique words. In other words, the whole field contains only unique words.

If it simplifies things, I have no commas or periods in my column of "text"

cool...cook up a user defined function, yum

cheers!

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-17 : 16:37:58

Something like this might help you:
http://sqlteam.com/item.asp?ItemID=5857

Michael

<Yoda>Use the Search page you must. Find the answer you will.

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-18 : 09:51:37

was MichaelP's link what you had in mind Page47?

JonC
Starting Member

1 Post

Posted - 2002-07-19 : 15:13:53

I was looking for the same thing, but didn't quite get the point of adding the tally table. I wrote the following to remove duplicates from a semi-colon delimited list. I have no idea what the performance implications of this versus a tally-table are but it seemed a little simpler. I would love to get feedback on what is good/bad about this.

DECLARE @KeywordList varchar(2000)
DECLARE @nextChar int, @currWord varchar(200), @UNIQVals varchar(2000)

SET @KeywordList = 'This;is;a;is;a;new;day'
SET @nextChar = 2
SET @UNIQVals = ''

-- append a ';' at the end if it is not already there so last word gets grabbed
IF LEN(@KeywordList) > 0 AND NOT(RIGHT(@KeywordList, 1) = ';')
SELECT @KeywordList = @KeywordList + ';'

WHILE @nextChar > 1
BEGIN
SELECT @nextChar = CHARINDEX(';', @KeywordList, 1) + 1
IF (@nextChar > 0)
BEGIN
SELECT @currWord = SUBSTRING(@KeywordList, 0, @nextChar)
SELECT @UNIQVals = @UNIQVals + @currWord
SELECT @KeywordList = REPLACE(@KeywordList, @currWord, '')
END
END

IF LEN(@UNIQVals) > 0
SELECT @UNIQVals = LEFT(@UniqVals, LEN(@UniqVals) -1)

print @UNIQVals

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-19 : 15:24:08

The "tally table" is your "final result"
You can them read from the "tally table" and write it to a table as needed.

Michael

<Yoda>Use the Search page you must. Find the answer you will.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-19 : 15:38:08

Sorry that I dropped this thread on you doc, Graz has docked my pay accordingly....

Yes, that article was exactly where I would start. Let us know how it goes.

<O>

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-22 : 10:45:58

Hey people

I actually do not require to perform this task anymore, but I have sort of developed a cursor method to accomplish this task. It's not the most efficient method, but it's what i know. the code is untested, so there may be some bugs.

a portion of the code was developed from:
http://www.sqlteam.com/item.asp?ItemID=1876

good luck jonC...

declare @separator char(1)
declare @separator_position int -- This is used to locate each separator character
declare @keyword_value varchar(102) -- this holds each array value as it is returned
declare @like_text varchar (102)
declare @UniqueText varchar(2000)
declare @Unique varchar (25)

set @separator = ','
set @keyword = @keyword + @separator
--@keyword is like your @keywordlist

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @keyword) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @keyword)
select @keyword_value = left(@keyword, @separator_position - 1)
select @like_text = '%' + @keyword_value + '%'

INSERT INTO #Results(Name)
SELECT v.Name
FROM tblTest AS v --replace this view with yours

DECLARE unique_cursor CURSOR FOR
SELECT name
FROM #Results
OPEN unique_cursor
FETCH NEXT FROM unique_cursor INTO @Unique

WHILE @@FETCH_STATUS = 0
BEGIN
SET @UniqueText=isnull(@UniqueText,'') + isnull(@Unique,'') + ' '
FETCH NEXT FROM unique_cursor INTO @Unique
END
CLOSE unique_cursor
DEALLOCATE unique_cursor

UPDATE tblX
SET Name = @UniqueText

drop table #Results
SET @Unique=null
SET @UniqueText=null

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-22 : 10:48:35

...what i do need DESPERATE help with is my "overflow" posting.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources