I have a column called Keywords in my Oracle database, Basically Keywords column contains all the data of other columns in particular row separated by _.
Example Table:
NAME PHONE_NUMBER COMPANY ADDRESS ZIPCODE KEYWORD
ABCD 9849523459 MICRO RAJAHMU 532819 ABCD_9849523459_MICRO_RAJAHMU_532819
ABCD 8628738646 INFOS KAKINAD 532775 ABCD_8628738646_INFOS_KAKINAD_532775
ABCD 8473874381 ICUBE RAVULAP 537238 ABCD_8473874381_ICUBE_RAVULAP_537238
Now, How can i get exact string match by using REGEXP_LIKE. When i'm using the below query
SELECT * FROM USER_DATA WHERE 1=1 AND REGEXP_LIKE ('KEYWORD', 'ABCD_MICRO_RAVULAP', 'i'));
It's returning 0 records
My expected output should be
NAME PHONE_NUMBER COMPANY ADDRESS ZIPCODE KEYWORD
ABCD 9849523459 MICRO RAJAHMU 532819 ABCD_9849523459_MICRO_RAJAHMU_532819
ABCD 8473874381 ICUBE RAVULAP 537238 ABCD_8473874381_ICUBE_RAVULAP_537238
I would be very grateful if anyone help me out.
Thanks Inadvance
CodePudding user response:
Something like this?
SQL> with user_data (name, keyword) as
2 (select 'ABCD', 'ABCD_9849523459_MICRO_RAJAHMU_532819' from dual union all
3 select 'DEFG', 'ABCD_8628738646_INFOS_KAKINAD_532775' from dual union all
4 select 'HIJK', 'ABCD_8473874381_ICUBE_RAVULAP_537238' from dual
5 )
6 select *
7 from user_data
8 where regexp_like(keyword, 'ABCD. MICRO', 'i');
NAME KEYWORD
---- ------------------------------------
ABCD ABCD_9849523459_MICRO_RAJAHMU_532819
SQL>
If you want to search the string for two words in any order, use |
operator:
SQL> with user_data (name, keyword) as
2 (select 'ABCD', 'ABCD_9849523459_MICRO_RAJAHMU_532819' from dual union all
3 select 'DEFG', 'ABCD_8628738646_INFOS_KAKINAD_532775' from dual union all
4 select 'HIJK', 'ABCD_8473874381_ICUBE_RAVULAP_537238' from dual union all
5 select 'LMNO', 'MICRO_241241242_ABCD_WHATEVER_241424' from dual
6 )
7 select *
8 from user_data
9 where regexp_like(keyword, '(ABCD). (MICRO)|(MICRO). (ABCD)', 'i');
NAME KEYWORD
---- ------------------------------------
ABCD ABCD_9849523459_MICRO_RAJAHMU_532819
LMNO MICRO_241241242_ABCD_WHATEVER_241424
SQL>
However, it isn't practical. Perhaps you should consider Oracle Text feature, then.
SQL> create table
2 user_data (name, keyword) as
3 (select 'ABCD', 'ABCD_9849523459_MICRO_RAJAHMU_532819' from dual union all
4 select 'DEFG', 'ABCD_8628738646_INFOS_KAKINAD_532775' from dual union all
5 select 'HIJK', 'ABCD_8473874381_ICUBE_RAVULAP_537238' from dual union all
6 select 'LMNO', 'MICRO_241241242_ABCD_WHATEVER_241424' from dual
7 );
Table created.
SQL> create index i1_ud on user_data(keyword) indextype is ctxsys.context;
Index created.
SQL> select *
2 from user_data
3 where contains (keyword, '$micro and abcd', 1) > 0;
NAME KEYWORD
---- ------------------------------------
ABCD ABCD_9849523459_MICRO_RAJAHMU_532819
LMNO MICRO_241241242_ABCD_WHATEVER_241424
SQL>
CodePudding user response:
Use IN
on the underlying columns rather than trying to parse the composite column:
SELECT *
FROM USER_DATA
WHERE (name, company, address) IN (
('ABCD', 'MICRO', 'RAVULAP'),
('MICRO', 'ABCD', 'RAVULAP')
);
If you want to compare case-insensitively then use UPPER
:
SELECT *
FROM USER_DATA
WHERE (UPPER(name), UPPER(company), UPPER(address)) IN (
('ABCD', 'MICRO', 'RAVULAP'),
('MICRO', 'ABCD', 'RAVULAP')
);
If you want to match a single triplet of values to the terms in any order then you can reverse the IN
clause:
SELECT *
FROM USER_DATA
WHERE ('ABCD', 'MICRO', 'RAVULAP') IN (
(UPPER(name), UPPER(company), UPPER(address)),
(UPPER(name), UPPER(address), UPPER(company)),
(UPPER(company), UPPER(name), UPPER(address)),
(UPPER(company), UPPER(address), UPPER(name)),
(UPPER(address), UPPER(name), UPPER(company)),
(UPPER(address), UPPER(company), UPPER(name))
);