I need to compare movie names from two systems. In both systems these names are entered manually by operators. I would like to compare them and give a rating on how close these names are equal.
Stripping special characters, and spaces is just not enough. It can happen that they key in sligthly different names. I've tried to use soundex but as we have over 15000 movie titles over the years i'm getting to many equal soundexes to use this as a comparison key.
I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707
What I want to to is to search for log posts that are in between two dates.
I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.
I'm currently working on a project that involves IP checking, here's my scenario:
A user enters an IP address of 10.174.55.65 let's say, I want to somehow be able to check and see if this IP address is in between column A (It's called ip) which consists the IP data of 10.174.0.0 and column B (It's called EndingIp) which has the IP data 10.174.255.255, so generally speaking I'm checking for the 3rd and 4th spots within the IP address and I'm trying to see if it's between the IP's in column A and B.
The thing is these are all strings, so how do I approch a stiuation like that within SQL?
Btw, I attached a picture of the table to be more clear.
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though. So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>" Is there an way to accomplish this inside a stored procedure?
Can someone please explain how to do a query using the soundex function? If I have the last name of Smith and I want to get similar matches with soundex, what would be the syntax? SELECT * FROM NAME WHERE LNAME = 'SMITH' Thanks, Kellie
Has anyone used SOUNDEX() practically? Everytime i look through bol searching for a function i end up playing with soundex and diffference but have never been able (or wanted to take the time) to understand the values they generate..
We've installed this on our server: http://microsoft.apress.com/feature/72/sql-server-soundexing
(all the udf attempts were way too slow; this uda is nice and quick)
But we have not been able to reproduce the supposed improved functionality. No matter what misspelled name we send it, soundex and soundex_nara return the same amount of clients (in a db of over half a million). The return codes are sometimes different for the two functions but it doesnt ever result in an actual difference in query results. Has anyone actually experienced a difference in returned results?
EDIT: Have a look at this: http://en.wikipedia.org/wiki/Levenshtein_distance
I have two tables in Access database. One table has 166,000 rows and another -75,000 rows.
Both tables consist of the ids, names and addresses.
I need to run a program, which was written in VBA, to find similar names and cities in these two tables, and then I need to populate the third table with id only from both tables. I forgot to mention I use Soundex function in Access. I try to run this program for 48 hours, and it still running. Do you think this is a good idea to use Access for this task ? Or could you please help me to find another solution?
Hello, Does the SOUNDEX function only apply to english (surnames) or can it be used for other languages e.g. Dutch. Well, it can be used ofc. but does it produce meaningful output ?
Disgruntled with Soundex I went looking for a better phonetic matching algorithm.
Turns out there is a rather good one called Metaphone, which comes in two variants (Simple and Double)
I could find the source for this in C++, but I wanted to have it as a user function.
So here it is:
CREATE FUNCTION dbo.Metaphone(@str as varchar(70)) RETURNS varchar (25) /* Metaphone Algorithm
Created by Lawrence Philips. Metaphone presented in article in "Computer Language" December 1990 issue. Translated into t-SQL by Keith Henry (keithh_AT_lbm-solutions.com)
*********** BEGIN METAPHONE RULES *********** Lawrence Philips' RULES follow: The 16 consonant sounds: |--- ZERO represents "th" | B X S K J T F H L M N P R 0 W Y Drop vowels
Exceptions: Beginning of word: "ae-", "gn", "kn-", "pn-", "wr-" ----> drop first letter Beginning of word: "x" ----> change to "s" Beginning of word: "wh-" ----> change to "w" Beginning of word: vowel ----> Keep it
Transformations: B ----> B unless at the end of word after "m", as in "dumb", "McComb"
C ----> X (sh) if "-cia-" or "-ch-" S if "-ci-", "-ce-", or "-cy-" SILENT if "-sci-", "-sce-", or "-scy-" K otherwise, including in "-sch-"
D ----> J if in "-dge-", "-dgy-", or "-dgi-" T otherwise
F ----> F
G ----> SILENT if in "-gh-" and not at end or before a vowel in "-gn" or "-gned" in "-dge-" etc., as in above rule J if before "i", or "e", or "y" if not double "gg" K otherwise
H ----> SILENT if after vowel and no vowel follows or after "-ch-", "-sh-", "-ph-", "-th-", "-gh-" H otherwise
J ----> J
K ----> SILENT if after "c" K otherwise
L ----> L
M ----> M
N ----> N
P ----> F if before "h" P otherwise
Q ----> K
R ----> R
S ----> X (sh) if before "h" or in "-sio-" or "-sia-" S otherwise
T ----> X (sh) if "-tia-" or "-tio-" 0 (th) if before "h" silent if in "-tch-" T otherwise
V ----> F
W ----> SILENT if not followed by a vowel W if followed by a vowel
X ----> KS
Y ----> SILENT if not followed by a vowel Y if followed by a vowel
Z ----> S */
AS BEGIN Declare@Result varchar(25), @str3char(3), @str2 char(2), @str1 char(1), @strp char(1), @strLen tinyint, @cnt tinyint
set @strLen = len(@str) set@cnt=1 set@Result=''
--Process beginning exceptions set @str2 = left(@str,2) if @str2 in ('ae', 'gn', 'kn', 'pn', 'wr') begin set @str = right(@str , @strLen - 1) set @strLen = @strLen - 1 end if@str2 = 'wh' begin set @str = 'w' + right(@str , @strLen - 2) set @strLen = @strLen - 1 end set @str1 = left(@str,1) if @str1= 'x' begin set @str = 's' + right(@str , @strLen - 1) end if @str1in ('a','e','i','o','u') begin set @str = right(@str , @strLen - 1) set @strLen = @strLen - 1 set@Result=@str1 end
while @cnt <= @strLen begin set @str1 = substring(@str,@cnt,1) if @cnt <> 1 set@strp=substring(@str,(@cnt-1),1) elseset@strp=' '
if @strp<> @str1 begin set @str2 = substring(@str,@cnt,2)
if @str1in('f','j','l','m','n','r') set@Result=@Result + @str1
if @str1='q'set @Result=@Result + 'k' if @str1='v'set @Result=@Result + 'f' if @str1='x'set @Result=@Result + 'ks' if @str1='z'set @Result=@Result + 's'
if @str1='b' if @cnt = @strLen if substring(@str,(@cnt - 1),1) <> 'm' set@Result=@Result + 'b' else set@Result=@Result + 'b'
if @str1='c'
if @str2 = 'ch' or substring(@str,@cnt,3) = 'cia' set@Result=@Result + 'x' else if @str2in('ci','ce','cy')and@strp<>'s' set@Result=@Result + 's' elseset@Result=@Result + 'k'
if @str1='d' if substring(@str,@cnt,3) in ('dge','dgy','dgi') set@Result=@Result + 'j' elseset@Result=@Result + 't'
if @str1='g' if substring(@str,(@cnt - 1),3) not in ('dge','dgy','dgi','dha','dhe','dhi','dho','dhu') if @str2 in ('gi', 'ge','gy') set@Result=@Result + 'j' else if(@str2<>'gn') or ((@str2<> 'gh') and ((@cnt + 1) <> @strLen)) set@Result=@Result + 'k'
if @str1='h' if (@strp not in ('a','e','i','o','u')) and (@str2 not in ('ha','he','hi','ho','hu')) if@strp not in ('c','s','p','t','g') set@Result=@Result + 'h'
if @str1='k' if @strp <> 'c' set@Result=@Result + 'k'
if @str1='p' if @str2 = 'ph' set@Result=@Result + 'f' else set@Result=@Result + 'p'
if @str1='s' if substring(@str,@cnt,3) in ('sia','sio') or @str2 = 'sh' set@Result=@Result + 'x' elseset@Result=@Result + 's'
if @str1='t' if substring(@str,@cnt,3) in ('tia','tio') set@Result=@Result + 'x' else if@str2='th' set@Result=@Result + '0' else if substring(@str,@cnt,3) <> 'tch' set@Result=@Result + 't'
if @str1='w' if @str2 not in('wa','we','wi','wo','wu') set@Result=@Result + 'w'
if @str1='y' if @str2 not in('ya','ye','yi','yo','yu') set@Result=@Result + 'y' end set @cnt=@cnt + 1 end RETURN @Result END
Hi AllWe are using soundex (and later tried Nysiis) for fuzzy name searchsoftware. But we faced a lot of problems the search accuracy was not verygood also we saw a lot of misses of relevant names.There are many problems other than precision and accuracy, with soundex andNYSIIS.e.g.Look for Smith and it will come-up with around 250 very popular last names.That dones not help much when a user is searching for "John Smith". Also, itdoes not return Creighton for Kryton as the search string.I googled a little and saw soming called NamiX. Without contacting thecompany ( arizcon.com ) directly, I wanted to get feedback from newsgroupsor people who are experts at this. Has anyone used this software? If so, Isit as good as they claim?Thanks a million in advance.Steve Creighton(please remove .antispam from email address) or post back your answers tothis group
We have an application we are developing that will run worldwide. When we are searching data for peoples names I would like to use Soundex but I am not sure it works effectivly for any language other than English. Does anyone know if it is effective in Dutch, German, French etc?
Hi thereI have this query in a stored procedureSELECT * FROM Product WHERE FREETEXT (*, @SearchString )When I run the query with the @SearchString = 'hoover' it return values but if I spelt it wrong (eg: hover) I have no value returnMy question is, How can I use the SOUNDEX in a full text searchRegards
Here's one for one of you SQL genuises out there. I really appreciate any help that I can get. There are several steps to this.Step 1:I have a database that holds timesheet records for users. These records are by week. I need a query that will return the username of the people that have entered 0 time for all of their timesheets in the database.Step 2:I need a query that will delete and timesheets that have 0 time entered after the last timesheet with time entered. If they entered time in week 3 but have 0 time for week 4 and 5 and then are withdrawn at week 5 I need to remove timesheets for week 4 and 5. Note, they could have entered 0 for week 2 as long as week three has time entered.Step 3: - THIS IS THE MONSTER-Since the timesheets are by week, I need to find the date that the last time was entered. The timesheet has a start-date then suntime, montime, tuetime, wedtime etc. Sunday would equal the week start-date. So, if time was last entered on Friday, the date that I'm looking for is the start-date + 5. My problem is, How do I go about doing this? I've been trying to do it all in a query to no avail.I really appreciate any assistance that anyone can offer.Thank you,Still learning........-Scott
I need to declare variables, like it is calculating on a section in other reports systems. Here I found only code for report where I can write some functions.
Anyway.. Did someone knows how to solve this problematic task??
Hi, Using Visual Web Developer and SQL Express, (coding in Csharp) I want to add a advanced search facility to my site. Problem is I do not know how to begin. The website is a business directory and displays company names and services that they provide. Here is where I am at.... So far I have made one query which allows users to search the database by "town" such as if they enter "London" in the text box, it returns the relevant records into a gridview. Rather than have a seperate text box for each query, I want to provide an advanced search box option so users can define their search and query the database by various criterias, such as geography, sector, name, number of employees in companies etc. I have seen compact advanced search boxes on many sites!. Replies greatly appreciated. prontonet
I want to query a database through a ASP.NET page and I want to set the page up like an advanced search page would look. My question is, how do I write the stored procedure and/or what value do I pass from ASP.NET to accomplish null or blank values. For instance, if someone wants to query by TRIP id and not vehicle ID then how do I write the where claue?
I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column: SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @CompanyID) AND (e.FacilityID = @FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'. I would imagine I would need a Declare @Status nvarchar(15) --- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Keri writes "I am still a SQL newbe, but have learned a lot in the past year. I am not creating databases, nor maintaining our SQL Server 2000. I am a report writer and use Query Analyzer for the base of my reports. I use Actuate to develop the reports. My question is, i am interested in getting a book that covers more advanced querying. I have the book The Guru's Guide to Transact-SQL, but am intested in other books to further my growth and education in SQL. Can you suggest some books that deal with queries-advanced primarily since i really am not designing or maintaining the DB. Thanks for any information you can give.
Here's a challenge for one of those database experts out there...
I want to join two tables in a Left Join format, with the first table being the one referencing the second table. Easy enough right? Well, I also want to be able to reference this second table from 3 different fields. For example:
My tables #1: video id, description ...etc... award1, award2, award3
#2: award id, description, image, etc...
As you can see, I want to be able to award more than 1 award from the "award" table to each video in the "video" table. I then want to be able to assign a variable to each field from the result of the SQL query so I can display all the information on one page for every video with every combination of possible awards.
I also, in my SQL Query, want a WHERE clause: WHERE video.id = $vidid (a variable from a previous page so this page only displays the video I want)
My mind says surely this must be possible, but I have no idea how to go about it. Can anyone give me any hints/help/pointers/advice on this? Even if this requires two SQL queries, I may be able to work with that, but I need to know. If I'm posting at the wrong place, or nobody here knows how to do this, then I would also really appreciate being pointed in the right direction.
Thanks in advance to anyone who even reads this and thinks over it. This is for a free online fan site http://www.lostvideo.net/ that I'm helping with some redesigns.
I have been able to get several basic databases to function both in playingaround and functional ones on the web but they have all been pretty simple.I am now trying to develop a database for the web using Access. What I amreally needing help with is how to actually reduce the number of record setsthat I think i am going to need. Here is what I am trying to do.I am having people sign up and create teams. So you come to the site and yousee Team 1, Team 2... Team 8. (These numbers will later be replaced by realnames given by the team captains and replaced in the database, this will bedone by the captain through the web)You pick a team you want to be on, enterthe info and you are placed on that team (in the database) I have two tablesin the database TeamRoster (everyone on all of the teams) and Teams (Listthe captains and the names of their team)So when I query the database to fill the tables I am confused as to how Ishould go about the query. As I see it I need to build a RS (Record Set) foreach team to get the name of the team for the appropriate table, thenpopulate it with the players for that team (repeating region) if there areeight teams, that means 16 RS. This does not seem right.Am I making any sense?Thanks for trying to understand all of this.Houston
I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:
Table X ID ------- 2,1,4, 2,5, 1, 3,1,2,4, ect... whereas the ID field in Table Y contains one ID in each row EX:
Table Y ID Color ------- ------- 1 Green 2 Blue 3 Red 4 Yellow 5 Orange ect...
Is there a way to join these two tables together? I need the output to be...
ID Color ------ ------- 2,1,4, Blue,Green,Yellow 2,5, Blue, Orange 1, Green 3,1,2,4, Red, Green, Blue, Yellow ect...
When one requirement says that MS SQL Server 2005 with Advanced Services is required, what does it mean by "Advanced Services"? Doesn't the full version of SQL2005 include all the advanced services? What are advanced services? I've seen the Express version with Advanced Services but this is the full version I'm talking about.
I have a table full of Latitudes, Longitudes, address, customername, etc. , I need to grab some input(Latitude, Longitude, range) from the user. So now I have a source lat, long(user) and destination lat, long(rows in dbase). I need to take the 2 points and compute a distance from the user given lat, long to every lat, long in the database and check that distance againt the range given from the user. If the distance is below the range, I need to put that row into a temp table and return the temp table at the end of the stored proc. As of right now I am completely lost and need some guidance. I would also like to be able to add the computed distance to a table. Here is the function and stored procedure i have so far...ALTER PROCEDURE [dbo].[sp_getDistance] @srcLat numeric(18,6), @srcLong numeric(18,6), @range intASBEGIN SET NOCOUNT ON; SELECT * FROM dbo.PL_CustomerGeoCode cg WHERE dbo.fn_computeDistance(@srcLat, cg.geocodeLat, @srcLong, cg.geocodeLong) < @rangeEND CREATE FUNCTION fn_computeDistance ( -- Add the parameters for the function here @lat1 numeric(18,6), @lat2 numeric(18,6), @long1 numeric(18,6), @long2 numeric(18,6))RETURNS numeric(18,6)ASBEGIN -- Declare the return variable here DECLARE @dist numeric(18,6) IF ((@lat1 = @lat2) AND (@long1 = @long2)) SELECT @dist = 0.0 ELSE IF (((sin(@lat1)*sin(@lat2))+(cos(@lat1)*cos(@lat2)*cos(@long1-@long2)))) > 1.0 SELECT @dist = 3963.1*acos(1.0) ELSE SELECT @dist = 3963.1*acos((sin(@lat1)*sin(@lat2))+(cos(@lat1)*cos(@lat2)*cos(@long1-@long2))) -- Return the result of the function RETURN @dist Thanks, Kyle
Hello, I am trying to bind a sqldatasource control to the gridview. I have selected the sqldatasource control and specified the connection string, on configure Select statement page under advanced options both the check marks Generate INSERT, UPDATE and DELETE Statements Use Optimistic Concurrency are disabled for me.I have a proper SQL Server database not an express data base, how do i get to generate the InserCommand, EditCommand etc Any help would be great .. relatively new here thanks
Hi! a) PROBLEM 1: I have set up my main database like this (of course I'm showing an abbreviation): company - int (identity)name - nchar I have several companies stored in the previous table. Now I have another table with messages: company - int (related with the company of the previous table)messageID - int (identity)contents - nchar company and messageID are the main key of this table. I want to set the messageID column to change automatically. Since I declared it as identity it is working fine, but I was looking to start it on 0 on every new company: Company messageID0 00 10 21 0 <- Here the company changed, so the messageID resets1 11 21 32 0 <- Again2 1 Any suggestions? b) PROBLEM 2:I have my database stored locally on my computer. When I finished working with the database it has a lot of data for testing. I want to upload the database to my hosting provider or to my customer's. But the identity columns keep incrementing since the last value of my tests, so it's kind of annoying to see values as: 1250, 223, etc. when I expect to see 0,1, 2 and so on. Also, for receipts this is a very important issue. How can I reset the identity fields? Thank you very much for your attention and help. CT
Advanced SQL generations options: generate INSERT, UPDATE, and DELETE statements is all greyed out? in my sql data source control.? I have made a brand new instance with sql server management express....have I missed something?
Hi There, I know this might be slighty off topic, but (apart from the CPU, RAM & DB size limits) does anyone know if Express SQL server has any performance / concurrent connection restrictions when compared to SQL 2005 Standard? Many thanks Ben
