0

I have table that has columns with email addresses delimited by ;:

TO                                              CC                                  BCC
--------------------------------------------------------------------------------------------------------------------
abc.def@xyz.com;an.yes@xyz.com;no.yes@xyz.com   anna.chris@xyz.com;DL_ABC@xyz.com   raj.alb@xyz.com;doug.amb@xyz.com

User gives an email address as the search criteria and I have to lookup against each of these columns to find the rows where any of the colums have the target email address.

Can anyone please help me out here?

I tried using reg_exp but dont have much experience there.

Noel
  • 10,152
  • 30
  • 45
  • 67
user2865588
  • 13
  • 6

2 Answers2

2

Simple way:

SELECT * FROM emailtable
WHERE INSTR(';' || to || ';' || cc || ';' || bcc || ';'
           ,';' || :search_parameter || ';') > 0;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Hi Jeffrey, Thanks for the input,I am able to get the output .But there is slight complexity here.User has an option to select the input as begins with/contains/ends with.user might not have the complete email address.can we use % character and can we modify the query. – user2865588 Oct 10 '13 at 06:30
  • In that case you'll need to parse the columns out - see http://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc – Jeffrey Kemp Oct 10 '13 at 10:27
0

From your requirement and from what I get from your comment on Jeffery Kemp's answer, I suggest you try the following:

SELECT *
  FROM emailtable
 WHERE lower(REPLACE ("TO" || ';' || cc || ';' || bcc, ' ', ''))
       LIKE '%' || lower(REPLACE (:search_parameter, ' ', '')) || '%'

The lower function helps us with a case insensitive search. REPLACE eliminates the spaces.

I hope this should give you a clue. This is the simplest way in which a part of email ID can be searched through the to, cc and bcc columns. Please modify the WHERE condition as per you requirements.

Rachcha
  • 8,486
  • 8
  • 48
  • 70
  • Hi Rachcha, the problem with your approach is that if the column contains space between the semi-colon and email it doesnt work. – user2865588 Oct 10 '13 at 08:00
  • What is your search string? Can you give me a few sample search strings? – Rachcha Oct 10 '13 at 08:09
  • Hi Rachcha, consider columns to=cc=bcc= " Syed.hussain@xyz.com ; Rajesh.S.Prabhu@xyz.com ; d.Saus@xyz.com " if user gives entry d.saus and criteria begins with – user2865588 Oct 10 '13 at 09:08
  • What is the difference between user entry and criteria in your case? – Rachcha Oct 10 '13 at 10:01
  • suppose user is not aware of the complete mail id but he knows email starts with d.saus/syed/Rajesh.so he gives d.saus/syed/Rajesh and selects a drop down which has criteria Begins with/Contains/Ends with.In this case begins with – user2865588 Oct 10 '13 at 10:07
  • Edited the answer, please check. – Rachcha Oct 10 '13 at 10:49