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 |
|
icw
Constraint Violating Yak Guru
378 Posts |
 Posted - 2009-10-16 : 09:15:24
|
| I have 9000 email addresses in table1In table two I have a field containing a large string of characters.Somewhere in that mess of characters is an emails address.So how do i find the value in table1 row1 anywhere in table 2.e.g.Table1Row Email1 Bill@test.com2 Fred@bbc.co.uk3 Sarah@itil.comTable2Row String1 hgjfsa76768fsaFred@bbc.co.uk%**D"(hj2 A765876^*&Bill@test.comkhlsadhf90'i3 A9676576£$SW23£fjSarah@itil.com*IUY |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-16 : 09:29:40
|
Search for CHARINDEX()
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-10-16 : 09:37:30
|
| WHERE PATINDEX('%'+email+'%', string) > 0you need to join tables :)Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ... |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-10-16 : 09:39:33
|
| charindex will also work. and will work better if you don't need to add any filters on the emailCan't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ... |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2009-10-16 : 09:56:02
|
| Thanks but i still can't work out how to use either of them in my query |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2009-10-16 : 10:04:05
|
| is it something likeSelect * from table1, table2 where charindex(table1.email,table2.string)>=1I'm scared that if I get it wrong i might kill the server |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-16 : 10:08:09
|
To have a test you can do it for just one certain ID in the WHERE clause...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
find a row from table1 in a string in table2