SQL Ethnic Name Matching

[vjp2.at]

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]

 

[Michel Walsh]

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

 

[Knut Stolze]

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.

 

[vjp2.at]

*+- "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]

 

[vjp2.at]

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]

 

[Michel Walsh]

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