|
This will work gracefully
declare @t table(num int)
insert into @t
select 12 union all select 43
union all select 31 union all select 35
declare @inputNum int
set @inputNum = 30
select num as [Closest Number] from(
select DENSE_RANK() over(order by abs(num - @inputNum)) [rank]
,num from @t
)X
where [rank] = 1
Output:
Closest Number
31
Note: Try with
Input Output
100 43
-1 12
39 43 & 35
(which is correct only because the difference is 4 for both the case). Henceforth I have used Dense_Rank()
Hope this helps
Niladri Biswas
modified on Monday, November 23, 2009 9:37 AM
|
|
|
|
|
thanks you so much Niladri Diswas....
VB.Net
|
|
|
|
|
Here is a better answer:
DECLARE @i int
SET @i = 30
SELECT TOP 1 Col1 FROM Table1 ORDER BY ABS(Col1-@i)
|
|
|
|
|
Hi,
Just 1 question to ask?
If the input is 39 what be the output? Is it 43 or 35 or both?
If the answer is the last one, then how
Shameel wrote: TOP 1 Col1
will help?
Niladri Biswas
|
|
|
|
|
If you remove the 'TOP 1' clause, you will see that the query returns both values. Adding TOP clause arbitrarily returns the first match.
|
|
|
|
|
Dear All
Thanks for you help.
Now i can get that task ready.
Best regards,
VB.Net
|
|
|
|
|
I am sure I have to explain this with more details..
People names in Arabic have more than one way to spell it.. for example, Hassan can be spelled with double or single S & Mohammad can be spelled (Mohammad) or (Mohd) or (Moh’d) or (Mohammed), etc… & my name can be spelled the way I spell it (Jassim) or (Jasim) or (Jassem) or (Jasem).. can you believe that?!!!
Now, what if I have the following name in my customers table in the full_name record
JASSIM MOHD HASSAN
And the user entered the following name:
JASIM MOHD HASAN
Or:
JASSIM MOHAMMAD HASSAN
Or any other different spelling way..
How can I search for similar names in the full_name records and alert the user for possible existing names?
|
|
|
|
|
This is not a perfect solution, but hopefully it might help.
If you separate out first, middle, and last name, then you might be able to look at sum of two instr(.) calls being greater than 0 when you search for the first few and last few characters. Given the names that match based on first and last name, then eliminate only based on middle as it might be left out.
You also might call a user defined function to count the # of matching characters (properly ordered) in each name on your smaller result set.
|
|
|
|
|
SOUNDEX is the standard way of handling phonetic searches in Sql Server but it can return lots of false matches.
|
|
|
|
|
Hi,
IMO you can solve such problems by using a "canonical form"; for each possible value, the canonical value should be defined somehow, in your example it could be the alphabetically first of all equivalent names, so in your Jassim example it would be Jasem. So you store the original value (Jassim) and the canonical value (Jasem); when a search is launched for say Jasim, you look up or compute its canonical value (again Jasem) and launch a search for that canonical value.
Again in your example, the canonical form would probably be defined by a database table, which also means you can store its unique ID rather than its string value.
Luc Pattyn [Forum Guidelines] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Hi,
I agree with Mr. Russel that though soundex will help in such situations but it will cause problems on the other hand by delivering extra results.
I suggest better use Metaphone(which was first described by Lawrence Philips in the December 1990 edition of Computer Language magazine) for serving your purpose
Look here for SQL Version
Metaphone (Simple and Double)
and here for Oracle Version(You will find the code in Listing A)
Consider algorithms other than Soundex
Hope this helps
Niladri Biswas
|
|
|
|
|
SOUNDEX function is the simplest way to start. But it matches only based on phonetic similarities. For example, 'Mohammed' and 'Mohamed' will match but not 'Mohd', since it has a completely different pronounciation. You need to write your own function to take care of such cases, probably by storing canonical forms of common names and comparing it with the input.
|
|
|
|
|
As noted by other posters, soundex is the easiest solution although it may not always work quite as expected.
A better "fuzzy matching" algorithm is the Levenstein algorithm which is pretty good, but you would probably have to code up your own solution for that and it might be a challenge to get it to perform over a large database. (Plenty of articles available if you Google for it.)
Or, you could use a full text search engine like Lucene or MS Search Server which might be overkill for you but which do have some nice built-in fuzzy search support. (They are intended for searching documents but you can get adaptors to run them over relational databases as well.)
|
|
|
|
|
Hi All ,
I'm beganer in SQL ,and I'm going to install aplication in the coustomer PC .My boss ask me to install Microsoft SQL Server 2005 Client version in the customer pc.
my qustion is , what is the "Microsoft SQL Server 2005 Client version"? is it the sql native client or not?
form where can i install it ? and how? is it free?
Many thanks,
|
|
|
|
|
Take a look at the information on Microsoft's SQL site[^], where you should be able to find what you need. I am not aware of a "client version" of SQL 2005 so you may need to clarify what it is that you will be running on the client PC. I assume that you already have the SQL Server installed somewhere.
|
|
|
|
|
If the client is to query your database from his own SQL Server, then SQL Server Compact or Express Edition might be needed. See http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
If you use non-generic connection strings in your project (e.g. a different vendor's OLE DB provider for SQL Server 2008) and installed a driver, then you might need to download the appropriate drivers to the client machine.
|
|
|
|
|
A database client is a desktop application that can be used to query a database. If the database is on a server that can be accessed over a network, your just need to install the client and connect to the server. For SQL Server, the most commonly used client is the SQL Server Management Studio (SSMS).
|
|
|
|
|
|
1) Is it possible to write queries using ADO on an in-memory System::DataTable that is not loaded in the database? Part of my motivation is that I may download a datasets from a website for one-time use, such as intraday equity data.
2) Is it possible to create a new table in a database from ADO's System::DataTable? For example, I've found that the same code might not work for creating an identity column in SQL Server, Oracle, and DB2.
---this works in DB2, but not Sql Server for example
CREATE TABLE ABBREV_LIST
( ID_NO INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) ,
ABBREV CHARACTER(4) NOT NULL CONSTRAINT UPPER_ABBREV CHECK (ABBREV = UPPER(ABBREV)),
PRIMARY KEY ( ID_NO ) )
|
|
|
|
|
You can't run sql against datasets but you can use Linq to DataSets instead.
|
|
|
|
|
Hello, I am looking at encryption in SQL Server 2008. I have the basics down but have a question about sizing the encrypted column.
I have two columns of data I want to encrypt. One 9 characters and one 20 characters. Currently VARCHAR(9) and VARCHAR(20). (Yes I know that the VAR means space savings.) What is the minimum VARBINARY needed for these, or a better question how do I calculate the VARBINARY size?
Thank you,
djj
|
|
|
|
|
Two suggestions.
1. SQL Server 2008 has built in encryption, why not use that?
2. Why not simply do a few tests to determine what size you need?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
The built in encryption is what I am looking at, at least I am using the BOL how to.
As to the tests I will do that but it would be nice to have an equation to calculate it.
djj
|
|
|
|
|
I am using SSRS 2008 in my application. For some items the item description contain the data as "5X7" DISNEY POSTCARD" in database. When I do the export to csv file in SSRS 2008 the text displayed as "5X7 DISNEY POSTCARD"" in csv file, it is placing the double qoute at the end of the text instead where it should be.
Please any one help me on this, I am running out of time.
Thanks in advance
-D
|
|
|
|
|
You have a problem that is probably not solvable, CSV/Excel/MS converters use the " character as the text delimiter and probably wraps the entire text field in ". Embedded quotes are probably not going to be supported, certainly in CSV where it is a control character.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
