Searching a database... - 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

General SQL Server Forums

New to SQL Server Programming

Searching a database...

Author

Topic

thehumantrashcan
Starting Member

2 Posts

Posted - 2006-07-26 : 08:05:31

Hi,

This is the first database I have ever created, so please bear with me.

I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).

I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)

SELECT
word
FROM dbo.words
WHERE word= 'hello'

This finds the word hello.

In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)

This comes back with a lot of possibilities (thousands), but is great in the sense that when I change any of the 25 letters the entire column automatically updates.

What I am trying to do is then take all of these possibilities and compare them against the dictionary.

I have written a line in excel which automatically creates a cell a bit like this, for the first couple of thousand possibilities:

WHERE word= 'abc' or word= 'fgm' or word= 'klm' or word= 'pqr' or word= 'uvw' or word= 'bcd' or word= 'ghi' or word= 'lmn' or word= 'qrs' or word= 'vwx'

I then whack this into the query from above and off it goes. The only problem is that the search takes ages, and because of limitations in excel I can't put more than a thousand or so words in the cell.

I am certain there is a faster way of searching through all the possibilities, any help would be much appreciated.

Thanks in advance

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 08:21:31

quote:
In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)

I'm trying to understand what you mean by this (given your examples). Is it this...?

declare @s varchar(25)
set @s = 'abcdefghijklmnopqrstuvwxy'

declare @numbers table (i tinyint)
insert @numbers select distinct number from master.dbo.spt_values where number between 1 and 25

select distinct substring(@s, a.i, b.i)
from @numbers a, (select * from @numbers where i between 3 and 10) b
where a.i + b.i <= len(@s) + 1

/*sample results
abc
abcd
abcde
abcdef
abcdefg
abcdefgh
abcdefghi
abcdefghij
bcd
bcde
bcdef
bcdefg
bcdefgh
...
*/

This gives 156 results, however - not 'thousands'. But every single combination would give a lot more than 'thousands'. So I'm confused.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-26 : 08:29:45

First :
>> simple database with 1 column and about 80,000 rows
It is actually a table u created to add rows & columns. that table is in a Database

Can u export the contents of Excel to a Database table ?
So that some other technique can be used to do ur comparison.

or at the time of creating the word check whether it is a valid word, checking against the sorted data of Database table.

eg. ur program created 'dof'
check --> select count(*) from dbo.words where word = 'dof' and if the results = 0, then disregard that

Add one more to Ryan's reply:

The combinations will be not thousands, may be trillions

Srinika

thehumantrashcan
Starting Member

2 Posts

Posted - 2006-07-26 : 09:41:18

quote:

...every single combination would give a lot more than 'thousands'...

Hi Ryan and Srinika,

So far I have limited it for words that are 3 to 5 letters long to limit the scope a bit and test how/if this works.

Secondly, Srinika, how doI go about importing from Excel into SQL server?

You're right it's a table, not a database

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-26 : 10:49:29

To import data
One (or few) time : Use Import Export Wizard

Frequently :
use a DTS or some script

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-26 : 11:12:52

Just a few comments:

The English alphabet has 26 letters, not 25. Did you decide to leave one out?

Every combination of even 5 letters is 11,881,376 combinations:
select power(26E,5E)

Every combination of 10 letters is 141,167,095,653,376 combinations:
select power(26E,10E)
You're going to need a lot of disk to hold that table, about 1300 terabytes

And finally, what possible use is this?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources