Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steves93
Starting Member
3 Posts |
 Posted - 2005-04-20 : 16:09:32
|
| I'm new to databases, trying to teach myself with a live project I have. Can anyone tell me if there's a relatively straightforward way to select records where any part of a text field matches any part of a different text field? For example, if a record has:field1="Bye, Bye Miss American Pie"andfield2="The American Constitution"They would be a match and would be returned in the SELECT statement?Thanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-20 : 16:33:14
|
| I don't understand the question. Your example doesn't have a match. Perhaps you need to explain further what you want.Tara |
 |
|
|
steves93
Starting Member
3 Posts |
Posted - 2005-04-20 : 16:49:39
|
| the example i gave is what i **want** to be considered a match, because there is a substring ("American") that is common to both fields. so what i'm hoping to find is a way to select records where two fields have a *partial* match of substrings within the fields, but the substrings could be anywhere within the field.any clearer?thanks |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-04-20 : 17:16:51
|
| Sure,And it would be a dogYou need a udf that returns a table of words parsed out by space, then you would need to join the 2 tables based on the udf, with a like...How much data are you talking about?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-20 : 18:09:53
|
| SELECT * FROM Table1 WHERE Column1 LIKE '%American%' OR Column2 LIKE '%American%'And that'll be slow as Brett indicated.Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-20 : 18:17:43
|
| What would you consider a partial match? If they both have the same word? If they both have the same letter?CODO ERGO SUM |
|
|
|
steves93
Starting Member
3 Posts |
Posted - 2005-04-20 : 21:47:41
|
| Brett:Talking about a pretty small database - less than 2000 records. I could do it in Excel, but it's a good opportunity for me to learn some SQL.tduggan:I don't want to specify the substrings - I basically want to match *any* substring in F1 with any substring of the same length in F2. See what I mean?MVJ:Ideally, I'd be able to specify a variable, which would be a substring length. So I could, say, call the variable @slength, and if @slength=8, then the select statement will search for any substring of length 8 in F2 that matches any substring of length 8 in F1.All:I think I might be talking about fuzzy logic. Am I right? I really want to clean up a database that has dupes that aren't literal. Like "Acme Products" and "Acme Products, Inc." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-20 : 21:56:23
|
| DECLARE @var1 varchar(30)SET @var1 = 'American'SELECT * FROM Table1 WHERE Column1 LIKE '%' + @var1 + '%' OR Column2 LIKE '%' + @var1 + '%'So now just put whatever string you want in @var1. You don't even have to worry about lengths.Now fuzzy logic will require SOUNDEX function, information can be found in SQL Server Books Online.Tara |
|
|
|
|
|
|
|
|
Really Easy (for you) SELECT Question