Home » SQL & PL/SQL » SQL & PL/SQL » Matching pattern with given word (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Matching pattern with given word [message #620963] |
Thu, 07 August 2014 09:07 |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I am stuck with a pattern matching, can you help me with below.
WITH ABUSIVE_WORD AS (SELECT 'ASSHOLE' ABS_WORD FROM DUAL),
CUST_NAMES AS (SELECT 'ASSAAAA' FIRSTNAME, 'HOLE' LASTNAME FROM DUAL
UNION
SELECT 'ASSAAAA', 'BBBHOLE' FROM DUAL
UNION
SELECT 'ASSHO', 'LEHOLE' FROM DUAL
UNION
SELECT 'ASS123', '345HOLE' FROM DUAL
UNION
SELECT 'MANU', 'BATHAM' FROM DUAL)
SELECT *
FROM CUST_NAMES A, ABUSIVE_WORD B
WHERE B.ABS_WORD LIKE ....
Output should be like:
FIRSTNAME LASTNAME
---------- ----------
ASS123 345HOLE
ASSAAAA BBBHOLE
ASSAAAA HOLE
ASSHO LEHOLE
Can a SQL made for this?
Problem is how to determine how many characters of abusive word will go in first name, and how many in last name.
Please suggest.
Thanks,
Manu
|
|
|
Re: Matching pattern with given word [message #620964 is a reply to message #620963] |
Thu, 07 August 2014 09:21 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Sorry, your rules are not clear. If you want to get those words in cust table which doesn't match with that in abuse table, then in the same query just do where cust.column != abuse.column
Or even, MINUS would give the results. But the question is, match what? First name or last name?
If that's not your requirement, then please explain the rules.
[Updated on: Thu, 07 August 2014 09:23] Report message to a moderator
|
|
|
Re: Matching pattern with given word [message #620965 is a reply to message #620964] |
Thu, 07 August 2014 09:27 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Also mention whether case matters while matching the words? If yes, then you could make the session case insensitive, or using regular expression case parameter, or... But first need more details.
|
|
|
|
|
|
|
|
|
|
Re: Matching pattern with given word [message #620995 is a reply to message #620966] |
Thu, 07 August 2014 10:59 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> WITH ABUSIVE_WORD AS (SELECT 'ASSHOLE' ABS_WORD FROM DUAL),
2 CUST_NAMES AS (SELECT 'ASSAAAA' FIRSTNAME, 'HOLE' LASTNAME FROM DUAL
3 UNION
4 SELECT 'ASSAAAA', 'BBBHOLE' FROM DUAL
5 UNION
6 SELECT 'ASSHO', 'LEHOLE' FROM DUAL
7 UNION
8 SELECT 'ASS123', '345HOLE' FROM DUAL
9 UNION
10 SELECT 'MANU', 'BATHAM' FROM DUAL)
11 select firstname, lastname, abs_word, idx split_at
12 from cust_names,
13 (select abs_word,
14 substr(abs_word, 1, column_value) beg,
15 substr(abs_word, column_value+1) end,
16 column_value idx
17 from ABUSIVE_WORD,
18 table(cast(multiset(select level from dual
19 connect by level <= length(abs_word))
20 as sys.odciNumberList)))
21 where firstname like beg||'%' and lastname like '%'||end
22 /
FIRSTNA LASTNAM ABS_WOR SPLIT_AT
------- ------- ------- ----------
ASS123 345HOLE ASSHOLE 3
ASSAAAA BBBHOLE ASSHOLE 3
ASSAAAA HOLE ASSHOLE 3
ASSHO LEHOLE ASSHOLE 3
ASSHO LEHOLE ASSHOLE 4
ASSHO LEHOLE ASSHOLE 5
|
|
|
Re: Matching pattern with given word [message #621001 is a reply to message #620995] |
Thu, 07 August 2014 11:13 |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
|
|
Appreciate it Michel. Almost Perfect.
I tried to understood, how you are doing that... Found a discrepancy.
It's returning 2 rows for:
SELECT 'DICKH', 'HHEAD' FROM DUAL
WITH ABUSIVE_WORD AS (SELECT 'ASSHOLE' ABS_WORD FROM DUAL
UNION
SELECT 'DICKHEAD' FROM DUAL),
CUST_NAMES AS (SELECT 'ASSAAAA' FIRSTNAME, 'HOLE' LASTNAME FROM DUAL
UNION
SELECT 'ASSAAAA', 'BBBHOLE' FROM DUAL
UNION
SELECT 'ASSHO', 'LEHOLE' FROM DUAL
UNION
SELECT 'ASS123', '345HOLE' FROM DUAL
UNION
SELECT 'MANU', 'BATHAM' FROM DUAL
UNION
SELECT 'MANU', 'BATHAM' FROM DUAL
UNION
SELECT 'DICKH', 'HHEAD' FROM DUAL
UNION
SELECT 'DICK', 'HEAD' FROM DUAL)
SELECT firstname,
lastname,
abs_word,
idx split_at
FROM cust_names,
(SELECT abs_word,
SUBSTR (abs_word, 1, COLUMN_VALUE) beg,
SUBSTR (abs_word, COLUMN_VALUE + 1) end,
COLUMN_VALUE idx
FROM ABUSIVE_WORD,
TABLE (
CAST (
MULTISET ( SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= LENGTH (abs_word)) AS SYS.
odciNumberList)))
WHERE firstname LIKE beg || '%' AND lastname LIKE '%' || end
/
Let me check what happens if I am running it on 2 million of records.
Thanks,
Manu
|
|
|
|
Re: Matching pattern with given word [message #621051 is a reply to message #621004] |
Fri, 08 August 2014 01:25 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There is a bug in the query when the abuse word is only in the last name.
Also if you have much cust_names to check, you'd have better performances materializing the inline on abuse words:
SQL> WITH ABUSIVE_WORD AS (SELECT 'ASSHOLE' ABS_WORD FROM DUAL
2 UNION all
3 SELECT 'DICKHEAD' FROM DUAL),
4 CUST_NAMES AS (SELECT 'ASSAAAA' FIRSTNAME, 'HOLE' LASTNAME FROM DUAL
5 UNION all
6 SELECT 'ASSAAAA', 'BBBHOLE' FROM DUAL
7 UNION all
8 SELECT 'ASSHO', 'LEHOLE' FROM DUAL
9 UNION all
10 SELECT 'ASS123', '345HOLE' FROM DUAL
11 UNION all
12 SELECT 'MANU', 'BATHAM' FROM DUAL
13 UNION all
14 SELECT 'MANU', 'BATHAM' FROM DUAL
15 UNION all
16 SELECT 'DICKH', 'HHEAD' FROM DUAL
17 UNION all
18 SELECT 'DICK', 'HEAD' FROM DUAL
19 UNION all
20 SELECT 'MIKE', 'DICKHEAD' FROM DUAL),
21 ABUSIVE_WORD2 as (
22 select abs_word,
23 substr(abs_word, 1, column_value) beg,
24 substr(abs_word, column_value+1) end,
25 column_value idx
26 from ABUSIVE_WORD,
27 table(cast(multiset(select level-1 from dual
28 connect by level <= length(abs_word)+1)
29 as sys.odciNumberList))
30 where rownum > 0
31 )
32 select firstname, lastname, abs_word, idx split_at
33 from cust_names, ABUSIVE_WORD2
34 where firstname like beg||'%' and lastname like '%'||end
35 /
FIRSTNA LASTNAME ABS_WORD SPLIT_AT
------- -------- -------- ----------
ASSAAAA HOLE ASSHOLE 3
ASSAAAA BBBHOLE ASSHOLE 3
ASSHO LEHOLE ASSHOLE 3
ASSHO LEHOLE ASSHOLE 4
ASSHO LEHOLE ASSHOLE 5
ASS123 345HOLE ASSHOLE 3
DICKH HHEAD DICKHEAD 4
DICKH HHEAD DICKHEAD 5
DICK HEAD DICKHEAD 4
MIKE DICKHEAD DICKHEAD 0
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 03:26:22 CDT 2024
|