|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
substring search in each row with a different wildcardI am looking for a solution for the following problem: I have 3 columns in my table of my Customers records; "Firstname", "Familyname" and the "Emailaddress".. Here i want to match the -Firstname and Familyname- with the emailadress, my aim here is to find the personalized emails. The mails like; Firstname.Familyn***@company.com Familyname.Firstn***@company.com Familyn***@company.com so that i can get a list of the customers that do not have a personalized mailaddress like; i***@company.com or m***@yahoo.com etc. so i have to do a substring search in each row with a different wildcard? is there a way doing this? thanks for the help. I haven't understood it correctly I guess :)
correct me if I am wrong. you can try something like this declare @company varchar(20) set @company = '@company.com' select * from table1 where emailaddress in (firstname +'.' + familyname + @comany,familyname +'.' + firstname + @comany,familyname+ @comany) Is it fine? Show quote "Axel" wrote: > Hi, > > I am looking for a solution for the following problem: > I have 3 columns in my table of my Customers records; > "Firstname", "Familyname" and the "Emailaddress".. > > Here i want to match the -Firstname and Familyname- with the emailadress, > my aim here is to find the personalized emails. The mails like; > Firstname.Familyn***@company.com > Familyname.Firstn***@company.com > Familyn***@company.com > so that i can get a list of the customers that do not have a personalized > mailaddress like; > i***@company.com or m***@yahoo.com etc. > > so i have to do a substring search in each row with a different wildcard? > is there a way doing this? > > thanks for the help. > > > > thank you for your answer, but thats not exactly what i'm looking for.
the company names are not set, it can be different for each row. the mailadresses i wrote were just examples, so the address can also be: FirstnameFamilyn***@mycompany1.com Familyname.Firstn***@yourcompany2.net firstn***@familyname.se ... .... etc. i mean i want to search if the Firstname and/or the Familyname are included somehow in the emailaddress. If the strings "Firstname" and/or "Familyname" are substrings of the "Emailaddress". i think i have to programm something that gets the "Firstname" and then maybe using the "LIKE %Firstname_of_the_49.Row%" for each row. The Wildcard will be set different for each row during the search. If i have 200 Clients that will be 200 Wildcards that is being set automatically during the search. i hope i could define my problem better.. thanks again Show quote "Omnibuzz" wrote: > I haven't understood it correctly I guess :) > correct me if I am wrong. > you can try something like this > > declare @company varchar(20) > set @company = '@company.com' > > select * from table1 > where > emailaddress in (firstname +'.' + familyname + @comany,familyname +'.' + > firstname + @comany,familyname+ @comany) > > Is it fine? > > > "Axel" wrote: > > > Hi, > > > > I am looking for a solution for the following problem: > > I have 3 columns in my table of my Customers records; > > "Firstname", "Familyname" and the "Emailaddress".. > > > > Here i want to match the -Firstname and Familyname- with the emailadress, > > my aim here is to find the personalized emails. The mails like; > > Firstname.Familyn***@company.com > > Familyname.Firstn***@company.com > > Familyn***@company.com > > so that i can get a list of the customers that do not have a personalized > > mailaddress like; > > i***@company.com or m***@yahoo.com etc. > > > > so i have to do a substring search in each row with a different wildcard? > > is there a way doing this? > > > > thanks for the help. > > > > > > > > Can you explain what this is?
"LIKE %Firstname_of_the_49.Row%" And what do you mean by 200 different wild cards. how will it be set automatically. and what is the logic? Or may be something like
select * from tbl1 where emailaddress like '%' + firstname + '%' or emailaddress like '%familyname%' maybe you can give some sample data if I am totally of the mark. thats bringing us near the answer but the missing thing here is that i have
to define the names(Firstname,Familyname) before the search. What i want is to define the Wildcards during the search for every row freshnew. I think i have to use a loop where i handle each row seperately in the same search? Show quote "Omnibuzz" wrote: > Or may be something like > select * from tbl1 > where > emailaddress like '%' + firstname + '%' or emailaddress like '%familyname%' > > maybe you can give some sample data if I am totally of the mark. what is the logic behind the wildcard and the rows?
can you give a sample data with the wildcard you will set for it and based on what condition. Maybe we can do without the looping :) Show quote "Axel" wrote: > thats bringing us near the answer but the missing thing here is that i have > to define the names(Firstname,Familyname) before the search. What i want is > to define the Wildcards during the search for every row freshnew. > I think i have to use a loop where i handle each row seperately in the same > search? > > "Omnibuzz" wrote: > > > Or may be something like > > select * from tbl1 > > where > > emailaddress like '%' + firstname + '%' or emailaddress like '%familyname%' > > > > maybe you can give some sample data if I am totally of the mark. For example I have a Table:
Milena Kazarova milena.kazar***@hochtifvsb.cz (ok) Radec Adanec i***@ikone.cz (not personalized) Ivo Regan re***@spaceplan.cz (ok) Mario Auer m*@metalli-auer.it (ok) Etiene Karton carbone.p***@spynet.de (not personalized) Somer i***@hadorn-metallbau.de (not ok) Töckli i***@haefliger-toeckli.ch (ok,toeckli is in the mailadress) and so on....... the idea is in the 3rd row for example getting "ivo" and "raga" and searching in the mailadress.. as "ragan" is a substring of it, it's included in our result.The same for Auer.. "Etiene" nor "Karton" are in the mail included, so it's not selected.... I hope i could explain it. Thanks for the answer. Show quote "Omnibuzz" wrote: > what is the logic behind the wildcard and the rows? > can you give a sample data with the wildcard you will set for it and based > on what condition. > Maybe we can do without the looping :) > > "Axel" wrote: > > > thats bringing us near the answer but the missing thing here is that i have > > to define the names(Firstname,Familyname) before the search. What i want is > > to define the Wildcards during the search for every row freshnew. > > I think i have to use a loop where i handle each row seperately in the same > > search? > > > > "Omnibuzz" wrote: > > > > > Or may be something like > > > select * from tbl1 > > > where > > > emailaddress like '%' + firstname + '%' or emailaddress like '%familyname%' > > > > > > maybe you can give some sample data if I am totally of the mark. Hi Axel,
I guess the substring logic won't work. I mean there should be a cut off. From what I understood.. >> "ivo" and "raga" and searching in the mailadress.. as "ragan" is a substring of it, it's included in our result. << I suppose you meant "raga" was a substring of "ragan". Well in that case so is "ra" or just "r". So can we say its okay if the mail address is something like djiks***@somemail.com has and its got a substring of "ragan" :) So we won't go much there.. the best is to try get an exact match of the firstname and the last name. And regarding changes in spelling (like in the last row). Töckli compared against Toeckli You may try using the soundex or difference function. Hope (atleast :) this helps. Axel (A***@discussions.microsoft.com) writes:
> thats bringing us near the answer but the missing thing here is that i Judging from you sample, firstname, familyname and emailaddress are > have to define the names(Firstname,Familyname) before the search. What i > want is to define the Wildcards during the search for every row > freshnew. I think i have to use a loop where i handle each row > seperately in the same search? all columns in the same table. There's no need to run an explicit loop. A SELECT statement will do that for you, and in a much more effecient way. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Axel (A***@discussions.microsoft.com) writes:
Show quote > I am looking for a solution for the following problem: One possible way to skin this cat:> I have 3 columns in my table of my Customers records; > "Firstname", "Familyname" and the "Emailaddress".. > > Here i want to match the -Firstname and Familyname- with the emailadress, > my aim here is to find the personalized emails. The mails like; > Firstname.Familyn***@company.com > Familyname.Firstn***@company.com > Familyn***@company.com > so that i can get a list of the customers that do not have a personalized > mailaddress like; > i***@company.com or m***@yahoo.com etc. > > so i have to do a substring search in each row with a different wildcard? > is there a way doing this? SELECT ... FROM tbl WHERE emailaddress LIKE firstname + '.' + familyname + '@%' OR emailaddress LIKE familyname + '.' + firstname + '@%' OR emailaddress LIKE firstname + '@%' OR emailaddress LIKE familyname + '@%' This may still not cover all cases, for instance the e-mail address may be george.w.b***@whitehouse.gov.us. Unforrunately SQL is not very good for this sort of pattern matching. If you are on SQL 2005, you could consider writing a CLR function that employes the Regex class in the .Net Framework. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks for the help, i think this would be my solution although it is not
going to deliver a 100% correct result. To go for a CLR Funktion would take some time for me, so i have to be happy with my result i think. Show quote "Erland Sommarskog" wrote: > Axel (A***@discussions.microsoft.com) writes: > > I am looking for a solution for the following problem: > > I have 3 columns in my table of my Customers records; > > "Firstname", "Familyname" and the "Emailaddress".. > > > > Here i want to match the -Firstname and Familyname- with the emailadress, > > my aim here is to find the personalized emails. The mails like; > > Firstname.Familyn***@company.com > > Familyname.Firstn***@company.com > > Familyn***@company.com > > so that i can get a list of the customers that do not have a personalized > > mailaddress like; > > i***@company.com or m***@yahoo.com etc. > > > > so i have to do a substring search in each row with a different wildcard? > > is there a way doing this? > > One possible way to skin this cat: > > SELECT ... > FROM tbl > WHERE emailaddress LIKE firstname + '.' + familyname + '@%' OR > emailaddress LIKE familyname + '.' + firstname + '@%' OR > emailaddress LIKE firstname + '@%' OR > emailaddress LIKE familyname + '@%' > > This may still not cover all cases, for instance the e-mail > address may be george.w.b***@whitehouse.gov.us. > > Unforrunately SQL is not very good for this sort of pattern matching. > If you are on SQL 2005, you could consider writing a CLR function > that employes the Regex class in the .Net Framework. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Hi Axel
Can't you use something like CHARINDEX? No wildcards would be needed, if I understand what you are asking for. SELECT ... FROM table WHERE charindex(firstname,emailaddress) > 0 or charindex(familyname, emailaddress) > 0 Show quote "Axel" <A***@discussions.microsoft.com> wrote in message news:DE383C31-FA3D-459D-A508-711AD06049DF@microsoft.com... > Hi, > > I am looking for a solution for the following problem: > I have 3 columns in my table of my Customers records; > "Firstname", "Familyname" and the "Emailaddress".. > > Here i want to match the -Firstname and Familyname- with the emailadress, > my aim here is to find the personalized emails. The mails like; > Firstname.Familyn***@company.com > Familyname.Firstn***@company.com > Familyn***@company.com > so that i can get a list of the customers that do not have a personalized > mailaddress like; > i***@company.com or m***@yahoo.com etc. > > so i have to do a substring search in each row with a different wildcard? > is there a way doing this? > > thanks for the help. > > > > Thanks, seems like this is the exact answer i was looking for.
Show quote "Kalen Delaney" wrote: > Hi Axel > > Can't you use something like CHARINDEX? No wildcards would be needed, if I > understand what you are asking for. > > SELECT ... > FROM table > WHERE charindex(firstname,emailaddress) > 0 or charindex(familyname, > emailaddress) > 0 > -- > HTH > Kalen Delaney, SQL Server MVP > www.solidqualitylearning.com > > > "Axel" <A***@discussions.microsoft.com> wrote in message > news:DE383C31-FA3D-459D-A508-711AD06049DF@microsoft.com... > > Hi, > > > > I am looking for a solution for the following problem: > > I have 3 columns in my table of my Customers records; > > "Firstname", "Familyname" and the "Emailaddress".. > > > > Here i want to match the -Firstname and Familyname- with the emailadress, > > my aim here is to find the personalized emails. The mails like; > > Firstname.Familyn***@company.com > > Familyname.Firstn***@company.com > > Familyn***@company.com > > so that i can get a list of the customers that do not have a personalized > > mailaddress like; > > i***@company.com or m***@yahoo.com etc. > > > > so i have to do a substring search in each row with a different wildcard? > > is there a way doing this? > > > > thanks for the help. > > > > > > > > > > > IF you want a list on non personalized emails, then don't start with a list
of personalized ones, get the non personalized ones directly. Select Firstname, Familyname, Emailaddress from Customers where Emailaddress not like '%Firstname%' and Emailaddress not like '%Familyname%' If you want to go with the first X letters of the name then change the above to: Emailaddress not like '%' + left(Familyname,3) + '%' Show quote "Axel" <A***@discussions.microsoft.com> wrote in message news:DE383C31-FA3D-459D-A508-711AD06049DF@microsoft.com... > Hi, > > I am looking for a solution for the following problem: > I have 3 columns in my table of my Customers records; > "Firstname", "Familyname" and the "Emailaddress".. > > Here i want to match the -Firstname and Familyname- with the emailadress, > my aim here is to find the personalized emails. The mails like; > Firstname.Familyn***@company.com > Familyname.Firstn***@company.com > Familyn***@company.com > so that i can get a list of the customers that do not have a personalized > mailaddress like; > i***@company.com or m***@yahoo.com etc. > > so i have to do a substring search in each row with a different wildcard? > is there a way doing this? > > thanks for the help. > > > >  |
|||||||||||||||||||||||
Other interesting topics