SQL Ethnic Name Matching

   I have a table of surnames and ethnicity.  I want to choose one ethnicity
and the number of characters going backwards from the end that should be
matched to approximate ethnicity.  This will be used to extract names of one
ethnicity form another table.  I'll be using MS Access initially, so I assume
their character matching routines (those in the help file look like the ones
in GW Basic) will do. (Dunno if they exist on other SQL.) Might someone
suggest the SQL code for this op?  Are LIKE and SIMILAR universal in SQL?  I
have a mismash of experiences with broad gaps.  I used IBM DB2 in the mid
1980s. Regex in the 1990s. And ACCESS in this century. But I am not really a
programmer. but I was trained as an engineer and when I take the time, I can
usually figure things out. But I don't always have the time.

				    - = -
 Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
	   http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
  ---{Nothing herein constitutes advice.  Everything fully disclaimed.}---
   [Homeland Security means private firearms not lazy obstructive guards]
 [Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]
0
vjp2
9/15/2007 3:01:41 AM
access.queries 6343 articles. 1 followers. Follow

5 Replies
818 Views

Similar Articles

[PageSpeed] 46

You can use LIKE if there is a strict pattern:

    "abcdef" LIKE "ab*"

or

    "abcdef"  LIKE  "*ef"


If there are a fixed number of patterns, you can put them in a table:

Patterns ' table Name
P_id, pattern ' fields
1, "*f"
2, "*ef"
3, "*def"
4, "*cdef"



You can then use a non-equi join:


SELECT y.name, p.pattern
FROM  yourTableName AS y LEFT JOIN Patterns As p
    ON y.name LIKE p.pattern
ORDER BY y.name, len(p.pattern) DESC


as example.




Hoping it may help,
Vanderghast, Access MVP





<vjp2.at@at.BioStrategist.dot.dot.com> wrote in message 
news:fcfhul$5gq$1@reader1.panix.com...
>   I have a table of surnames and ethnicity.  I want to choose one 
> ethnicity
> and the number of characters going backwards from the end that should be
> matched to approximate ethnicity.  This will be used to extract names of 
> one
> ethnicity form another table.  I'll be using MS Access initially, so I 
> assume
> their character matching routines (those in the help file look like the 
> ones
> in GW Basic) will do. (Dunno if they exist on other SQL.) Might someone
> suggest the SQL code for this op?  Are LIKE and SIMILAR universal in SQL? 
> I
> have a mismash of experiences with broad gaps.  I used IBM DB2 in the mid
> 1980s. Regex in the 1990s. And ACCESS in this century. But I am not really 
> a
> programmer. but I was trained as an engineer and when I take the time, I 
> can
> usually figure things out. But I don't always have the time.
>
>     - = -
> Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
>    http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
>  ---{Nothing herein constitutes advice.  Everything fully disclaimed.}---
>   [Homeland Security means private firearms not lazy obstructive guards]
> [Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for 
> Bimbos] 


0
Michel
9/17/2007 6:33:37 PM
vjp2.at@at.BioStrategist.dot.dot.com wrote:

>    I have a table of surnames and ethnicity.  I want to choose one
>    ethnicity
> and the number of characters going backwards from the end that should be
> matched to approximate ethnicity.  This will be used to extract names of
> one
> ethnicity form another table.  I'll be using MS Access initially, so I
> assume their character matching routines (those in the help file look like
> the ones in GW Basic) will do. (Dunno if they exist on other SQL.) Might
> someone
> suggest the SQL code for this op?  Are LIKE and SIMILAR universal in SQL? 
> I
> have a mismash of experiences with broad gaps.  I used IBM DB2 in the mid
> 1980s. Regex in the 1990s. And ACCESS in this century. But I am not really
> a programmer. but I was trained as an engineer and when I take the time, I
> can usually figure things out. But I don't always have the time.

Your description sounds to me as if data mining would be a good idea.

-- 
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
0
Knut
9/24/2007 4:08:22 PM
*+-    "abcdef"  LIKE  "*ef"

*+-SELECT y.name, p.pattern
*+-FROM  yourTableName AS y LEFT JOIN Patterns As p
*+-    ON y.name LIKE p.pattern
*+-ORDER BY y.name, len(p.pattern) DESC

How can I make old.lastname.pattern the last three 
letters of old.lastname preceded by an asterisk?

ie,  
    if new.lastname like old.lastname.pattern 
            then new.ethnicdesc = old.ethnicdesc

(How can I make this into SQL syntax?)

Many thanks.

				    - = -
 Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
	   http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
  ---{Nothing herein constitutes advice.  Everything fully disclaimed.}---
   [Homeland Security means private firearms not lazy obstructive guards]
 [Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]
0
vjp2
9/25/2007 3:27:53 AM
Imagine their is a way to use SELECT and JOIN to vectorise the
operation I am trying to do.


				    - = -
 Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
	   http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
  ---{Nothing herein constitutes advice.  Everything fully disclaimed.}---
   [Homeland Security means private firearms not lazy obstructive guards]
 [Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]
0
vjp2
9/25/2007 4:36:21 AM
UPDATE yourTableName  INNER JOIN Patterns
    ON yourTableName.name LIKE Patterns.pattern
SET yourTableName.ethnicdesc=patterns.ethnicdesc


should do, in Jet.


Hoping it may help,
Vanderghast, Access MVP


<vjp2.at@at.BioStrategist.dot.dot.com> wrote in message 
news:fd9v7p$8v6$1@reader1.panix.com...
> *+-    "abcdef"  LIKE  "*ef"
>
> *+-SELECT y.name, p.pattern
> *+-FROM  yourTableName AS y LEFT JOIN Patterns As p
> *+-    ON y.name LIKE p.pattern
> *+-ORDER BY y.name, len(p.pattern) DESC
>
> How can I make old.lastname.pattern the last three
> letters of old.lastname preceded by an asterisk?
>
> ie,
>    if new.lastname like old.lastname.pattern
>            then new.ethnicdesc = old.ethnicdesc
>
> (How can I make this into SQL syntax?)
>
> Many thanks.
>
>     - = -
> Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
>    http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
>  ---{Nothing herein constitutes advice.  Everything fully disclaimed.}---
>   [Homeland Security means private firearms not lazy obstructive guards]
> [Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for 
> Bimbos] 


0
Michel
9/25/2007 1:56:24 PM
Reply:

Similar Artilces:

Paste Name Questions
Hello, I am working in an Excel Spreadsheet that is used as a template and has about 30 page. I am revamping it and sometimes when I try to copy a worksheet within the workbook, I get an error such as: "A formula or sheet you want to move or copy contains the name '\P' which already exists on the destination worksheet. Do you want to use this version of the name?" o To use the name as defined in the destination sheet, click Yes o To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box. I have...

Re-install a database/company after deleting the DB in SQL EM.
Can anyone list all the tables that need to be cleaned out if a company database is deleted in Enterprise Manager before the company is deleted in GP? I have mistakenly done this and would like to clear everything out and set up that same database name again. No backup yet as it was a brand-new migration database ( a poor excuse I admit) that had the migration run on it. Thought we had it by clearing out the related company entries in the SY01500, DU0000xx, SY40500, SY40502? and a few more tables, but the new company setup still bombs on the very last step of the SQL setup (loading d...

SQL Server Ranking: Count Consecutive Values / Reset
Dear All, I have the following set of query results, the last column being derived using ROW_NUMBER and partitioned by TradDate, Account, PLInd using an OrderBy subclause in the OVER determined on EntryTime. The values for TradeNum are roughly correct except that I would want to calculate consecutive Winners and Losers only i.e. in my example the second subset of "Winners" would start counting at 1, 2 rather than continue the previous subset value at 5,6 etc. Can anyone suggest where I am going wrong please? Ultimately I will be "max'ing" the TradeNum to g...

Shared workbook user name list not updating
We have an issue with a shared excel workbook. Basically, when a user exits the spreadsheet, their name is sometimes left in the list of users who are accessing the worksheet. Does anyone know why this happens, and if so, how to stop it? Thanks Jo, When I have seen this, the name has remained in the list because the user shut Excel down abnormally. Usually, the user had used Task Manager to abort Excel, or they thought their workstation had "frozen" and they just rebooted. I don't know how to prevent this, but you can open the file from time to time and clean out the na...

proposal of similar names when writing a new message
hi, when you write a new mail you have the nice feature that you get a list of similar addresses. this list is growing with every new address you use. well, this is cool. but when i close my outlook or reboot my pc the list is empty again and outlook must learn them all again.. is this a normal "feature" or a misconfiguration or a real "feature"? where is this list saved?? michael ...

Huge Internet Mail Service (server name) mailbox
Does anyone know why this mailbox would be so huge and how would you go about reducing it's size? Thanks -Greg 5.5? Apply SP4 if you havent already, take a good backup , then run ISINTEG -FIX -PRI -TEST MAILBOX On Mon, 11 Apr 2005 06:31:03 -0700, "Greg Graeff" <GregGraeff@discussions.microsoft.com> wrote: >Does anyone know why this mailbox would be so huge and how would you go about >reducing it's size? > >Thanks > >-Greg ...

Money Express Sign-in name
How do I change the default sign-in name in msmoney express? I changed my email address in passport but the old name always defaults at sign-in time. I don't use Money express, but perhaps disabling it through the tools->options part of MS Money, reboot and the check the box again. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK sp...

Found it: How to map logical drive to network drive name
Please disregard previous post. I found the solution in an earlier post: From: Adrian (AndersonAdrian@hotmail.com) Subject: Converting a mapped drive path to UNC View this article only Newsgroups: microsoft.public.vc.mfc Date: 2000/04/24 It works, but man...is this ugly code country or what!...:) ...

Create columnar report with field names once per page
I have learned out to create multi-columned Access Columnar Reports with the aid of the Report Wizard and altering the File>Page Setup... However I would like to generate a multi column report for several records but only list the Field Name titles once on the left hand side of the paper. Check out the KB article at http://support.microsoft.com/kb/208491/en-us. -- Duane Hookom Microsoft Access MVP "Ch." wrote: > I have learned out to create multi-columned Access Columnar Reports with the > aid of the Report Wizard and altering the File>Page Setup... However I...

can a form display current table field names?
Hello, I need to create a form that will display the current field names resulting from a make-table querythat can change from audit to audit. This way the user is always aware of the "current" field names in their dataset. Currently, a form displays the structure from a linked (text formt) table that I have to manually export every time the make-table query changes and I'd like this to be generated from a macro or from code. Any Ideas? I thank you, John_Lehmus Maine, USA This article illustrates how you can list the field names, types, and descriptions for a table: ...

Can one forbid / globally disable the "matching" of imported statements?
While I'm in general quite happy with money's book keeping and visualization capabilities, one thing that infuriates me again and again is its idiotic "matching" functionality! I.e. again and again when I import a .qif file from my payment application Money tries to "match" these entries with items already in the list, thereby obviously trying to do the worst possible job possible. None of the entries CAN match at that point, since they don't exist in Money then and so Money obviously tries to pair them up with more or less random entries in the list (we...

lookup and match
I have 2 worksheets. I have a main list that contains 1260 product codes and I need to lookup and match product codes from the second list. The main list contains a rebate price that I would like to show up next to each matching product code. Sounds simple enough but I've been stuck for 3days. Can anyone please help? I'm not sure which function to use and which steps to take for the formula. Hi Genietrapped I can definitely help you. However, there is not enough information in your e-mail to know what to tell you to solve your prolbme and it would be quicker for me ...

How do you get the company name to appear in the task list?
I go to a contact and assign a task but the company name does not appear in the task list. Any folder view shows only the data in that folder, even though Outlook = lets you think otherwise by allowing you to add fields from different = types of items in the folder. The task contains no company name = information unless you add it manually or write a little VBA code. For = an example, see http://www.outlookcode.com/codedetail.aspx?id=3D566 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outloo...

sql sp4 to server
If I apply sql 2000 sp4 do I also need to apply sp4 to the desktop engine on my laptop clients using outlook client? I meant to say that If I apply sql sp4 to the CRM server (which is running sql locally) will I also need to aplly sp4 to the MSDE instances on my laptop users PC's using the Outlook client? Sorry just a little anxious\nervous when it comes to CRM 1.2. Trying to get to CRM 3.0 "dstinnett" wrote: > If I apply sql 2000 sp4 do I also need to apply sp4 to the desktop engine on > my laptop clients using outlook client? Yes sp4 is required for the msde. ...

automatic name finding in To: box feature
I can't find out how to make my Outlook 2000 program automatically fill the names I type in the To: box. I know how to do it in Outlook XP, but can't find it in Outlook 2000. Is there a Help Book that I can download for Office 2000 or just Outlook 2000? Outlook 2000 doesn't have an AutoComplete feature -- it uses Auto Resolution instead. See the following article for a description of how this works: http://www.slipstick.com/emo/2000/up001108.htm#autocomplete -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only ...

MOM 2005 and SQL Server 2005
Hello, I'm not having much luck searching through the knowledge base or the newsgroup to find definate information. What is the current status of trying to run MOM 2005 using SQL Server 2005 instead of SQL Server 2000? Some posts seem to indicate SP1 should allow it to work, even if it's still technically unsupported. Is that the case? Since I'll get charged for another media set from our vendor to get the new MOM SP1 CD since everything I've seen says slipstreaming isn't possible. I'd like to make sure it'll work before I purchase a second copy of the CD set....

CRM and SQL Report Services (SRS)
Hello All, I'm trying to get CRM and SQL Report Services to run on the same system. Everything seems to work with the exception of changing a data source and creating a subscription (big problem). I'm running server 2003 with SQL server 2000, CRM 1.2. SQL Report Services runs fine until I install CRM on the same system. I'm guessing it has to do with both CRM and SRS running in the same web application; but I'm just guessing on that. Any ideas, Caleb caleb@gsicrm.com wrote in message news:<1113288043.187227.183910@g14g2000cwa.googlegroups.com>... > Hello All, ...

Repeating data on matching cells
When the data column C matches the data in column A, I need the data in columns C and D to be repeated for all matching rows. For example: This is my data: a b c d 009701449-2 071712 009701449-2 071712 009701449-2 071712 009701452-4 008710 009701449-2 071712 009701455-7 008710 009701449-2 071712 009701460-1 008710 009701449-2 071712 009701465-6 058617 009701449-2 071712 009701467-8 059602 009701452-4 008710 009701468-9 059602 009701452-4 008710 009701455-7 008710 009701455-7 008710 009701460-1 008110 009701465-6 058617 0097...

CRM Report erro on SQL server web
Can anyone help out with this --tya Server Error in '/Reports' Application. -------------------------------------------------------------------------------- Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0006: Metadata file 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll' could not be found Source Error: [No relevant source lines] Source File: Line:...

How to mention a particular sheet name while opening excel with VB script. #2
Hi Pls say me 1. How to mention a particular sheet name while opening excel with VB script. 2.how to know the rowcount and column count in excel using vb script. as soon as possible .very urgent Maybe Private Sub Workbook_Open() Sheets("Sheet1").Select 'change to your sheet End Sub Mike "refina" wrote: > Hi Pls say me > 1. How to mention a particular sheet name while opening excel with VB script. > 2.how to know the rowcount and column count in excel using vb script. > > > as soon as possible .very urgent > > U can use usedrange.col...

How to change cell name??(Possible??)
Hi! Is it possible to change the name of the cell ? For eg. inspite of A1 i can change & write Tom1.And in place of A B C D it shows name given by me.For eg. Tom Mark Brett etc.... -- Little Master ------------------------------------------------------------------------ Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28227 View this thread: http://www.excelforum.com/showthread.php?threadid=479352 using ctrl F3, you can change the individual cells. No you can't do that. The best you can do is add the names in row 1 and then remove col...

Write Conflict in Access with SQL server Tables
I have a write conflict as described by several others when trying to update or add new information into forms or tables in an access database linked to SQL Server tables through an ODBC connection. The problem happens after I have changed the tables through SQL Server Enterprise manager and then go back to working through Access. I have refreshed the tables in SQL Server, deleted and re-linked the Tables in Access and have even made a new ODBC connection and again re-linked the Tables. Any suggestions? RW Some type of fields will give you trouble with the optimistic locking mecanism ...

convert from last name, first name TO first name, last name
How can I convert my contacts (as a group menaing all of them at once0 from the last name, first name to first name last name, without having to enter EACH cotnact and perform it almost 400 times? Also, in what format would you suggest trying to print my contacts showing fields name mobile, home, work phone and email address? I assume there is a way to make used defined fields? You'll need to create a post with enough information that others could understand it. How are we to know which field or view you are trying to "convert?" Be specific. I would suggest pri...

Change text name based on file name
On my spreadsheet in cell A1 I have a text label containing the word "Department" What I want is when I save the file as Schedule 1 (Division).xls I want cell A1 change from "Department" to "Division" based upon what is within the parentheses in this example Any tips will be appreciated. Thank you. If I understand you, you want to return the portion of the wb's name that is within parentheses. If so, try this formula in cell A1. After doing a "Save As..." you will have to press F9 to update. Minimal testing: =MID(CELL("filename&quo...

Formula for Worsheet Name
What is the formula that will give the worksheet namein cell A1 ? Thanks in advance -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=494559 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul Sheppard" <Paul.Sheppard.20ad1y_1134993603.7414@excelforum-nospam.com> wrote in messa...