Search each token in a varchar column split with a delimiter

Question

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

TO                                              CC                                  BCC
--------------------------------------------------------------------------------------------------------------------
[email protected];[email protected];[email protected]   [email protected];[email protected]   [email protected];[email protected]

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.

Try [INSTR](http://psoug.org/reference/substr_instr.html) instead of reg_exp. — Burhan Khalid, Oct 10 '13 at 06:11

score 2 · Answer 1 · answered Oct 10 '13 at 06:08

2

Simple way:

SELECT * FROM emailtable
WHERE INSTR(';' || to || ';' || cc || ';' || bcc || ';'
           ,';' || :search_parameter || ';') > 0;

answered Oct 10 '13 at 06:08

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

Rachcha · Answer 2 · 2013-10-10T10:49:25.970

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.

edited Oct 10 '13 at 10:49

answered Oct 10 '13 at 07:30

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= " [email protected] ; [email protected] ; [email protected] " 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

Search each token in a varchar column split with a delimiter

2 Answers2