"Fuzzy search" component - Database Discussion Boards

Sam Heller30-Jul-07 22:16

30-Jul-07 22:16

Sorry I don't follow this. Could you explain, possibly using some code, this solution in more depth.

I really want to be able to pass a parameter of say "AD" and then create some code that could work out the rest of the letters needed and then perform something like the following.

SELECT *<br />
FROM SOMEWHERE <br />
WHERE nameofperson LIKE a%<br />
AND nameofperson LIKE b%<br />
AND nameofperson LIKE c%<br />
AND nameofperson LIKE d%

Re: Alphabetical Select Statement

andyharman30-Jul-07 22:44

andyharman

30-Jul-07 22:44

Hi Sam

Assuming that you are using SQL-Server, try:

select * from somewhere where nameofperson like '[ABCDEF]%'

You might want to check that you have a case-insensitive collation set for your database - otherwise you will need to use the "Upper()" function around your "nameofperson" column.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: Alphabetical Select Statement

Sam Heller30-Jul-07 22:47

Sam Heller

30-Jul-07 22:47

Hey good work, it works. I didn't think it would be that simple. Turns out it does pull both lower and uppper case records as well. Thanks for your help.

Re: Alphabetical Select Statement

Paul Conrad31-Jul-07 3:01

Paul Conrad

31-Jul-07 3:01

Andy's solution is a very good one Smile | :)

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: Alphabetical Select Statement

Sam Heller31-Jul-07 3:05

Sam Heller

31-Jul-07 3:05

Yeah i think it kind of trumps yours for the fact that I can skip letters out in the way he proposes. Thanks for your solution though. It does indeed produce the results I require.

Re: Alphabetical Select Statement

Paul Conrad31-Jul-07 13:10

Paul Conrad

31-Jul-07 13:10

Andy's works fine as long as you aren't asked to do something like A-Q Smile | :)

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: Alphabetical Select Statement

Paul Conrad31-Jul-07 3:00

Paul Conrad

31-Jul-07 3:00

Here is what I was thinking of:

SELECT * FROM sometable WHERE ( Left( someField,1) >= 'A' AND Left( someField,1) <='D' )

This would return all rows in sometable where the first letter is A, B C, D. To do something like AD, you could just have this query in a stored procedure and have it parse your parameter A and D in this case, and plug it into the WHERE clause...

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

"Fuzzy search" component

afendi30-Jul-07 11:49

afendi

30-Jul-07 11:49

Good day.

I'd like to add "fuzzy search" functionality to my application.

"Fuzzy search" in this topic means selecting (from DB table) rows, which have "fuzzy search" coefficient (calculated using etalon string) not less some_predefined_const. Fuzzy search coefficient calculating algorithm can be various.

So with etalon string "Margaret" "fuzzy search" can find "Nargaret", "Margoret", "Margret" etc.

IMHO time to develop, test and tune code must be quite long. I prefer to buy such "fuzzy search" component.

Does anybody know where can I get such component - server version (SQL 2005) or client version (.NET)?
How much can such component cost?

Thanks.

Select from a DataTable

MohammadAmiry30-Jul-07 8:05

MohammadAmiry

30-Jul-07 8:05

I have a DataSet which has Two tables: TableAll, TableQueried
TabelAll has all data available.
What I want is to use a command to select only those rows in TableAll and copy them to TabelQueried which are LIKE some pattern.
I knew how to do this when I had a database and used connections; but now there is no database.
How should I do anything like this command:
"SElECT * FROM TABELALL WHERE Data LIKE [pattern]"

Re: Select from a DataTable

Aaron VanWieren30-Jul-07 13:56

Aaron VanWieren

30-Jul-07 13:56

I assume you are talking about an ADO.NET way?

If so look at DataTable.Select(CriteriaString).

I think the select method has an override for an order by clause. Criteriastring is a string in the format of:

(column name) expressions such as = or like or in; google datatable.select to get list of expressions. And then the criteria, very similar to a where condition in a sql statement.

IE for examaple

DataRow[] rows = myDataTable.Select("Column1 = 25","column1 asc")

This will return an array of DataRows which you can then work with.

Hope this helps!

Aaron

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

Re: Select from a DataTable

MohammadAmiry31-Jul-07 2:55

MohammadAmiry

31-Jul-07 2:55

THANK YOU!! Wink | ;)

You really helped me out! Rose | [Rose]

I was JUST looking for this.

Re: Select from a DataTable

Aaron VanWieren31-Jul-07 8:55

Aaron VanWieren

31-Jul-07 8:55

The only caveat you need to remember is that the datatable select consumes quite a bit of memory as it has to go through every record in the table. Be sure to set a primary key column and index if possible.

Aaron

View Next & Previous Data using C#

Saiyed Alam30-Jul-07 6:06

Saiyed Alam

30-Jul-07 6:06

Is there any way to view next or previous data of SQL database without using SQLDataAdapter and DataSet. If there is then how?? And If there is no way then what's the procedure of using these two thing?

Re: View Next & Previous Data using C#

Paul Conrad30-Jul-07 13:59

Paul Conrad

30-Jul-07 13:59

Saiyed Alam wrote:
view next or previous data of SQL database without using SQLDataAdapter and DataSet

Why do you want to not use DataAdapter or DataSet? Any particular reason?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: View Next & Previous Data using C#

Mike Dimmick30-Jul-07 23:43

Mike Dimmick

30-Jul-07 23:43

The SqlDataReader can only present a forward-only, read-only result set. This is a change from the old ADO Recordset days mainly because the programming model tended to lead to applications holding result sets, and hence transactions, open for a long time and blocking each other. ADO.NET offers a disconnected-only model to avoid these problems (if appropriate, a provider can offer a connected model, for example SQL Server Compact Edition, which is an in-process database engine, offers the SqlCeResultSet class which provides scrolling and updateable result sets).

The normal approach is simply to construct a SqlDataAdapter object, set the SelectCommand property to a command object with the appropriate command, then call Fill on the data adapter to get the results.

If using data binding of Windows Forms controls, the CurrencyManager class can help manage the current position in the DataTable or DataSet.

Stability. What an interesting concept. -- Chris Maunder

DTS Query

A.Muthunagai30-Jul-07 1:20

A.Muthunagai

30-Jul-07 1:20

Hi All,
I want to pass the table from Server to My PC. Particular table values only I want. How can I fetch the values from Server using DTS.
I'm using Sql server 2005.Give me any sample.

Thanks in Advance

Re: DTS Query

Rob Graham30-Jul-07 4:08

Rob Graham

30-Jul-07 4:08

I think you need to rephrase your question. As it currently stands, it makes very little sense. What do you mean "pass the table"
It sounds like you want to query a table for some specific rows using some application running on your PC to get data from some server (on your local network?). Why you would think DTS was appropriate is beyond me...unless you want to store the data locally in a different database, after extracting it from SQL 2k5.

Any sample for DTS?

A.Muthunagai30-Jul-07 23:10

A.Muthunagai

30-Jul-07 23:10

Hi,
I want to know more abt DTS. If u have any samples,msg me.
I Created the sample dtsx in BI successfully. Is there any way to see the coding for dtsx?

Thanks in advance

Indexing SQL 2005 Database!!!

kibromg30-Jul-07 0:37

kibromg

30-Jul-07 0:37

Hi All,

I am not really a database guy,And i wanted to do indexing on my tables as they now have a slow response when i run a qurey!!!

Could you please give the steps to index the database so that i can get a better performance?

Thanks and best regards

Re: Indexing SQL 2005 Database!!!

Rob Graham30-Jul-07 4:15

Rob Graham

30-Jul-07 4:15

Some links that might help:
Beginners article on database tuning[^]
SQL Server 2k5 Database tuning advisor tutorial[^]

SQL Float Rounding... or something :-)

MatthysDT30-Jul-07 0:07

MatthysDT

30-Jul-07 0:07

This SQL Statement:

SELECT CAST(2.78 AS FLOAT)

Returns in SQL Server 2005 as: 2.78
But....
Returns in SQL Server 2000 as: 2.7799999999999998

Obviously 2.78 <> 2.7799999999999998

Where can I set my SQL Server 2000 to not do this!

_______________________________________________________________________
http://www.readytogiveup.com/[^]
"you can't forget something you never knew..." M. Du Toit

Re: SQL Float Rounding... or something :-)

Krish - KP30-Jul-07 0:12

Krish - KP

30-Jul-07 0:12

decimal can be used instead of Float

CAST(2.78 AS Decimal(5,2))

Regards
KP

Re: SQL Float Rounding... or something :-)

Mike Dimmick30-Jul-07 12:05

Mike Dimmick

30-Jul-07 12:05

Ah, time to post What Every Computer Scientist Should Know About Floating Point[^] again.

The value 2.78 cannot be stored exactly in a 32-bit floating point field. The closest approximation has the bit pattern 40 31 EB 85 (in hex), which is 1.011 0001 1110 1011 1000 0101 left-shifted by 1, i.e. 10.11 0001 1110 1011 1000 0101. (See IEEE 754[^] on Wikipedia.)

Converting to decimal you get:

1 * 2 ^  1 = 2 +                2
0 * 2 ^  0 
1 * 2 ^ -1 = 0.5 +              2.5
1 * 2 ^ -2 = 0.25 +             2.75
0 * 2 ^ -3
0 * 2 ^ -4
0 * 2 ^ -5
1 * 2 ^ -6 = 0.015625           2.765625
1 * 2 ^ -7 = 0.0078125          2.7734375
1 * 2 ^ -8 = 0.00390625         2.77734375
1 * 2 ^ -9 = 0.001953125        2.779296875

You can see that the approximation is getting closer to 2.78, but the exact value is not possible as 0.03 (the residual after the first four bits) is not representable as a sum of negative powers of two. In the same way 1/3 is not representable exactly as a sum of negative powers of 10, or a decimal as we normally call it - the closest we can get is 0.33333 (and the 3s continue infinitely).

You can also see by the fact that the approximation is getting longer in decimal digits that a complete representation of the binary value will take a lot of space. I believe that a binary floating point value is always fully representable as a decimal (two dividing evenly into ten) but the space requirement may be unacceptable. Therefore, the value is almost always shown rounded to a certain number of places.

SQL Server does not have particularly good display-control features. That's not what it's for - it's for retrieving data efficiently. Instead you should use the typed-dataset mechanisms of whatever your client programming language or environment is - for example, the SqlDataReader in .NET - to retrieve the data without interpretation, then format the result using the programming language/environment's features (e.g. String.Format).

If you want to preserve the decimals accurately, you should use one of the scaled integer data types (e.g. decimal which is a synonym for numeric). These simply record the value without the decimal point, and a decimal exponent (e.g. here it would record 278 with an exponent of -2: 2.78 = 278 * 10 ^ -2).

Stability. What an interesting concept. -- Chris Maunder

Re: SQL Float Rounding... or something :-)

MatthysDT30-Jul-07 21:36

MatthysDT

30-Jul-07 21:36

_______________________________________________________________________
http://www.readytogiveup.com/[^]
"you can't forget something you never knew..." M. Du Toit

Re: SQL Float Rounding... or something :-)

Colin Angus Mackay30-Jul-07 22:45

Colin Angus Mackay

30-Jul-07 22:45

5 for the best explanation ever.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.