MS SQL Query

Hello,
I am trying to write a sql query to retrieve all unique words from multiple rows from one column:

Table

Row 1: This is title one.
Row 2: This is title two.

Results:

This
is
title
one
two

Thanks in advance.
MaxKroyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrRanyCommented:
I don't think there is a way to do that you want in one query.
It can be done by a stored procedure.
First you create a new table (in memory) of one column with unique status.
Then you parse each row of your source table and insert  into the new table each word.
Anyway, try to check this one.
0
Mark WillsTopic AdvisorCommented:
Yep, can be done (I think, or, would at least like to try if MS SQL )...

But is this MySQL or MS SQL ? Your zone says MySQL but your title says MS SQL
0
Mark WillsTopic AdvisorCommented:
OK, assuming it is MS SQL of at least SQL 2005 or above (please advise)

then try the code below...

it is a single query, but starts by building some test data first - you wont have to do that when you go to test against your actual data source (after playing with the example below).

-- first up lets build a test table and some dummy data so we can play with our query

if object_id('tempdb..#my_table','U') is not null drop table #my_table
go

create table #my_table (id int identity, comments varchar(200))
go

insert #my_table (comments)
select 'This is title one.' as comments
union all
select 'This is title two.'
union all
select 'This ! is yet another title two.'
union all
select 'And what about puntuation?'
union all
select 'Or maybe "quotes"'
go

-- now we have some test data to play with, lets see what kind of query we can come up with...

;with words_cte as
(
select rtrim(ltrim(substring(comments,n,charindex(' ',comments+' ',n+1)-n))) as The_Word
from ( select replace(replace(replace(replace(comments,'"',' '),'!',' '),'?',' '),'.',' ') as comments from #my_table ) srce
cross join (select number n from master..spt_values where type = 'p')  numbers
where substring(' '+comments,n,1) = ' '
and n <= len(comments+' ') 
)
select the_word --, count(*) as occurances 
from words_cte
where len(the_word) > 0  -- eliminate noise words as much as possible so make it 1,2 or 3 instead of zero.
group by the_word

-- to test the above query against your own test data, 
-- first replace the column "comments" with the real column name, 
-- and the table "#my_table" with your table name

Open in new window


Now, those punctuation marks might get annoying, so, we might need to do something about those... The example above simply checks for a few of the more obvious, but you could have a lot more (like comma, semicolon or basically any special character).

The master..spt_values is a special table (comes standard and almost a system table)  and has numbers up to 2048. For longer string you might need to build a numbers table.

You can read about spt-values in my Article : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume that you are using MS SQL Server (due to the tags), and not MySQL, please confirm (so we can also move the Q to the correct zone)

if that is correct, please create this function dbo.ParmsToList() in your database:
http://www.experts-exchange.com/A_1536.html

then, your query goes like this:
select distinct value
 from (select f.value from yourtable t cross apply dbo.ParmsToList(t.your_column_name, ' ') f) sq

Open in new window

0
MaxKroyAuthor Commented:
MSSQL. For some reason the tags are getting messed up when I submit the question.
0
MaxKroyAuthor Commented:
Mark,
You are truly an expert. Thanks for the query. Cheers.

Max
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.