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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #620966 is a reply to message #620965] Thu, 07 August 2014 09:36 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Oh, sorry, I thought I wrote the requirements in words, somehow missed it.

1. Case doesn't matter.

2. Find all those records where First few characters of First Name || Last Few characters of Last name = Abusive Word

Thanks for quick reply.

Manu

[Updated on: Thu, 07 August 2014 09:43]

Report message to a moderator

Re: Matching pattern with given word [message #620967 is a reply to message #620966] Thu, 07 August 2014 09:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Substr(first_name,1,3) concatenate it to substr of last name for 4 characters from reverse. Match this concatenated word with that in abuse table.
Re: Matching pattern with given word [message #620968 is a reply to message #620967] Thu, 07 August 2014 09:47 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Doesn't satisfy my requirement Lalit.

2. Find all those records where First few characters of First Name || Last Few characters of Last name = Abusive Word


Characters are not fixed, otherwise I should have used substr.

First Name = AsFlo
Last Name = abcsHole

And that's why I have written:

Problem is how to determine how many characters of abusive word will go in first name, and how many in last name.


Thanks,
Manu
Re: Matching pattern with given word [message #620969 is a reply to message #620968] Thu, 07 August 2014 09:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Should "Ass" in first name and "hole" in last name be together as set of characters, might be anywhere.

P.S. : Didn't you get any other example other than such a word!
Re: Matching pattern with given word [message #620971 is a reply to message #620969] Thu, 07 August 2014 10:09 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

No, it can't be anywhere.

It must be in the beginning for firstname or the ending of lastname.

If you find

'A' in beginning of firstname and 'sshole' in the end of lastname.
'As' in the beginning of firstname and 'sshole' in the end of lastname.
'Ass' in the beginning of firstname and 'shole' in the end of lastname.
'Assh' in the beginning of firstname and 'ole' in the end of lastname.
so on

Abusive words may be different.
You can take below as other example.

'D' in beginning of firstname, and 'ickhead' in the end of lastname.

Let me know, if I required to explain with more examples.

Manu
Re: Matching pattern with given word [message #620974 is a reply to message #620971] Thu, 07 August 2014 10:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I gave you enough information to achieve your task on your own, all you need is to have a case construct or similar conditional selection. Rest everyrhing should remain same as I suggested above.

One more hint : LIKE

Good luck!

[Updated on: Thu, 07 August 2014 10:22]

Report message to a moderator

Re: Matching pattern with given word [message #620993 is a reply to message #620974] Thu, 07 August 2014 10:50 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

I tried, but failed. I would love to see how you will perform it with case and like operator.

Thanks for bearing with me.

Manu
Re: Matching pattern with given word [message #620995 is a reply to message #620966] Thu, 07 August 2014 10:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #621004 is a reply to message #621001] Thu, 07 August 2014 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Found a discrepancy. It's returning 2 rows for:


Yes like it returns 3 rows for the last name in the first example you posted, it is not a discrepancy. My query shows why you can have multiple matches (last column): it depends where you split the abusive word and you can split it at different point.
This is expected by the query, if you don't want this then remove the last column and use a DISTINCT, or keep only the first match.

Re: Matching pattern with given word [message #621051 is a reply to message #621004] Fri, 08 August 2014 01:25 Go to previous messageGo to next message
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

Re: Matching pattern with given word [message #621092 is a reply to message #621051] Fri, 08 August 2014 14:52 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
LOL, Nice colorful topic for a Friday afternoon. Thanks.
Re: Matching pattern with given word [message #621102 is a reply to message #620963] Fri, 08 August 2014 20:30 Go to previous message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Actually few conditions I was explicitly checking with IN clause.

Thanks for all your help Michel.

Cheers,
Manu
Previous Topic: how to use constant
Next Topic: list of invalid objects
Goto Forum:
  


Current Time: Fri Mar 29 03:26:22 CDT 2024