1

Topic: Check the user regexp_like

Good afternoon.
Prompt  how correctly to solve such task:
The irrelevant user, having an e-mail annak@ is underlined as the receiver of the documents which real receiver is an e-mail jannak@
email_cb Is a list of all addresses of receivers of the document (a type text box )
osname - the user name, can be other combination of user name further
select * from email, empl
where upper (email.email_cb) like ' % ' || upper (trim (empl.osname)) || ' ' || ' %'
Whether in line with all  email_cb to check up is before osname gaps, punctuation signs (; ")
regexp_like (upper (email.email_cb), ' [^ [:space:] [:punct:]] ')
It is possible to solve it with the help  expressions? How correctly to write down it?

2

Re: Check the user regexp_like

Sweetira wrote:

it is possible to solve it with the help  expressions

by means of a microscope and such mother it is possible to solve much. Only here what exactly?

3

Re: Check the user regexp_like

Sweetira;
Give ive we try once again and this time Russian.
You have a table of actual users:

 empl
id number
osname varchar2

You have a table of documents and all receivers of documents

 email
doc_id number
email_cb clob 

In which in the field email_cb through a separator contains all receivers of the document, for example

 doc_id email_cb
1 vasya.pupkin@company.com, jannak@company.com 

You need email_cb to find from a field all users, at which first part of a name to  records from the table empl.osname?

4

Re: Check the user regexp_like

feagor;
Yes so.

5

Re: Check the user regexp_like

empl
id number
osname varchar2 - vasya.pupkin
select *
from daual
where instr (' vasya.pupkin@company.com, jannak@company.com ', ' vasya.pupkin ' || ' ')> 0

6

Re: Check the user regexp_like

Sweetira;

with empl as (
select ' jannak ' osname from dual
union all
select ' vasya.pupkin ' osname from dual
union all
select ' an23_us' osname from dual);
email as (
select 1 id, ' jannak@company.com,annak@company.com,vasya.pupkin@mail.ru,an2_us@gmail.com ' e_list from dual
)
,t as (
select
trim (regexp_substr (t.e_list, ' [^,] + ', 1, levels.column_value)) email
from
email t;
table (cast (multiset (select level from dual connect by level <= length (regexp_replace (t.e_list, ' [^,] + ')) + 1) as sys. OdciNumberList)) levels
)
SELECT * FROM t, empl
where regexp_replace (t.email, '. + $ ') =empl.osname
EMAIL OSNAME
jannak@company.com jannak
vasya.pupkin@mail.ru vasya.pupkin

7

Re: Check the user regexp_like

feagooor;
And what for this construction from table (cast (multiset...?

with empl as (
select ' jannak ' osname from dual
union all
select ' vasya.pupkin ' osname from dual
union all
select ' an23_us' osname from dual);
email as (
select 1 id, ' jannak@company.com,annak@company.com,vasya.pupkin@mail.ru,an2_us@gmail.com ' e_list from dual
)
,t as (
select
trim (regexp_substr (t.e_list, ' [^,] + ', 1, level)) email
from email t
connect by regexp_substr (t.e_list, ' [^,] + ', 1, level) is not null
)
SELECT * FROM t, empl
where regexp_replace (t.email, '. + $ ') =empl.osname
;

8

Re: Check the user regexp_like

9

Re: Check the user regexp_like

rpovarov;
The first that got on demand list to table:)

10

Re: Check the user regexp_like

rpovarov;
Not easier regexp_substr (str, ' ([^ |,] +) ', 1, level, ' i ', 1)?
.....
stax