OraFAQ Forum: SQL & PL/SQL » Need to match combination string at database

Home » SQL & PL/SQL » SQL & PL/SQL » Need to match combination string at database (Oracle 11g, AIX server)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to match combination string at database [message #657484]

Thu, 10 November 2016 01:19

Shan.kumar
Messages: 5
Registered: November 2016

Junior Member

Hi,

I need to create a sql or pl/sql to match different combination of string separated by comma(,) to a database column and fetch the rows.

My example_string='TEST-001,TEST-002,TEST-003,TEST-004';

And my database columns contains like below,

COMBINATION                                 DESCRIPTION
===========                                 =============
TEST-001,TEST-002,TEST-003,TEST-004         Description 1
TEST-001,TEST-007,TEST-005,TEST-004         Description 2
TEST-001,TEST-003,TEST-004,TEST-002         Description 3
TEST-008,TEST-009,TEST-010,TEST-011         Description 4

I need to create a query based on above example_string, which will give me output of below rows.

TEST-001,TEST-002,TEST-003,TEST-004         Description 1
TEST-001,TEST-003,TEST-004,TEST-002         Description 3

As I don't have big knowledge on pl/sql and comparatively new on that, iam stuck. Any help will be appreciated.

Thanks

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 10 November 2016 01:43] by Moderator

Report message to a moderator

Re: Need to match combination string at database [message #657486 is a reply to message #657484]

Thu, 10 November 2016 02:06

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I am not sure what you are trying to do. The definition of the table would help. But I think you may want to use a context text index, so you could write a query such as

select * from your_table where contains(your_column,'TEST-001') > 0 and contains ..... ;

Report message to a moderator

Re: Need to match combination string at database [message #657488 is a reply to message #657486]

Thu, 10 November 2016 02:21

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or without "Text":

select * from your_table where ','||your_column||',' like '%,TEST-001,%' or ... ;

with a FBI on "','||your_column||','".

Report message to a moderator

Re: Need to match combination string at database [message #657489 is a reply to message #657486]

Thu, 10 November 2016 02:22

Shan.kumar
Messages: 5
Registered: November 2016

Junior Member

Hi John,

Thanks for your response. What I want is below,

According to requirement, we will have a table, as I explained. This table will contain information of product's material. This materials are the combination. Now, same combination of material can make different product. But the ordering of the materials in "Combination" column may differ. But I need to find out all the products with the given combination.

As my input material combination is 'TEST-001,TEST-002,TEST-003,TEST-004'.

And my query should return below values as they consist of same material(just ordering is different separated by comma) but products are different. As you can see the description is different.

TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-003,TEST-004,TEST-002 Description 3

Hope this input will help you to understand the issue. Please help. Iam not sure how to proceed.

Thanks

Report message to a moderator

Re: Need to match combination string at database [message #657490 is a reply to message #657489]

Thu, 10 November 2016 02:23

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

You have been given two solutions already. And you have still not provided the description of your table.

Report message to a moderator

Re: Need to match combination string at database [message #657491 is a reply to message #657489]

Thu, 10 November 2016 02:23

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And forgot the moderator bit:

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Need to match combination string at database [message #657492 is a reply to message #657486]

Thu, 10 November 2016 02:24

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

The following method uses an Oracle Text context index.

-- test data:

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
  2  /

COMBINATION                         DESCRIPTION
----------------------------------- ---------------
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-007,TEST-005,TEST-004 Description 2
TEST-001,TEST-003,TEST-004,TEST-002 Description 3
TEST-008,TEST-009,TEST-010,TEST-011 Description 4

4 rows selected.

-- lexer with hyphen as printjoin:

SCOTT@orcl_12.1.0.2.0> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
  3    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '-');
  4  END;
  5  /

PL/SQL procedure successfully completed.

-- Oracle Text context index that uses lexer:

SCOTT@orcl_12.1.0.2.0> CREATE INDEX combination_idx ON test_tab (combination)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('LEXER test_lex')
  4  /

Index created.

-- variable:

SCOTT@orcl_12.1.0.2.0> VARIABLE example_string VARCHAR2(100)
SCOTT@orcl_12.1.0.2.0> EXEC :example_string := 'TEST-001,TEST-002,TEST-003,TEST-004'

PL/SQL procedure successfully completed.

-- SQL query:

SCOTT@orcl_12.1.0.2.0> SELECT combination, description
  2  FROM   test_tab
  3  WHERE  CONTAINS
  4  	      (combination,
  5  	       '{' || REPLACE (:example_string, ',', '} AND {') || '}') > 0
  6  /

COMBINATION                         DESCRIPTION
----------------------------------- ---------------
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-003,TEST-004,TEST-002 Description 3

2 rows selected.

Report message to a moderator

Re: Need to match combination string at database [message #657497 is a reply to message #657492]

Thu, 10 November 2016 04:31

Shan.kumar
Messages: 5
Registered: November 2016

Junior Member

Hi Boehmer,

Many thanks everyone and specially you. The solution almost worked fine, but there is one issue. First let me explain few points,

My Table contains below combination,
COMBINATION DESCRIPTION
ART-001,ART-002,ART-003,ART-004,ART-005,ART-006 SPR1
ART-007,ART-008,ART-009,ART-010,ART-011,ART-012 SPR2
ART-003,ART-005,ART-001,ART-004,ART-002,ART-006 SPR3
ART-001,ART-002,ART-003,ART-004 SPR4
ART-001,ART-002,ART-008,ART-011,ART-005,ART-012 SPR5
ART-004,ART-002,ART-001,ART-003 SPR6

I have created the below index as of now,

CREATE INDEX myindex ON TEST_MATCH(COMBINATION)
INDEXTYPE IS CTXSYS.CONTEXT;

And fired your query,

SELECT * FROM TEST_MATCH
WHERE CONTAINS
(COMBINATION,
'{' || REPLACE ('ART-001,ART-002,ART-003,ART-004,ART-005,ART-006', ',', '} AND {') || '}') > 0;

That gave the expected result. But when I changed the input as below,

SELECT * FROM TEST_MATCH
WHERE CONTAINS
(COMBINATION,
'{' || REPLACE ('ART-001,ART-002,ART-003,ART-004', ',', '} AND {') || '}') > 0;

I got the below unexpected result,

ART-001,ART-002,ART-003,ART-004,ART-005,ART-006 SPR1
ART-003,ART-005,ART-001,ART-004,ART-002,ART-006 SPR3
ART-001,ART-002,ART-003,ART-004 SPR4
ART-004,ART-002,ART-001,ART-003 SPR6

The problem is, with the above query, I don't expect 1st and 2nd row in result. Is there any way I can stick to the result for 3rd and 4th row for this input? Because SPR1 and SPR2 contains some extra materials. So these products should not come with these material combination(ART-001,ART-002,ART-003,ART-004) from business point of view.

Thanks

Report message to a moderator

Re: Need to match combination string at database [message #657499 is a reply to message #657497]

Thu, 10 November 2016 04:36

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 10 November 2016 09:23

And forgot the moderator bit:

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please follow the requirements, don't you think Barbara's post is nicer and easier to read than yours?

Report message to a moderator

Re: Need to match combination string at database [message #657506 is a reply to message #657497]

Thu, 10 November 2016 22:05

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

In that case, I probably wouldn't use Oracle Text. Instead I would do something like below, separating the strings, then putting them back together in order, then comparing the results for equality.

-- test data:

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_match
  2  /

COMBINATION                                        DESCRIPTION
-------------------------------------------------- ---------------
ART-001,ART-002,ART-003,ART-004,ART-005,ART-006    SPR1
ART-007,ART-008,ART-009,ART-010,ART-011,ART-012    SPR2
ART-003,ART-005,ART-001,ART-004,ART-002,ART-006    SPR3
ART-001,ART-002,ART-003,ART-004                    SPR4
ART-001,ART-002,ART-008,ART-011,ART-005,ART-012    SPR5
ART-004,ART-002,ART-001,ART-003                    SPR6

6 rows selected.

-- variable:

SCOTT@orcl_12.1.0.2.0> VARIABLE example_string VARCHAR2(35)
SCOTT@orcl_12.1.0.2.0> EXEC :example_string := 'ART-001,ART-002,ART-003,ART-004'

PL/SQL procedure successfully completed.

-- SQL query:

SCOTT@orcl_12.1.0.2.0> SELECT t1.combination, description
  2  FROM   (SELECT combination, description,
  3  		    LISTAGG (vals, ',') WITHIN GROUP (ORDER BY vals) combo
  4  	     FROM   (SELECT combination, description,
  5  			    REGEXP_SUBSTR (combination, '[^,]+', 1, COLUMN_VALUE) vals
  6  		     FROM   test_match,
  7  			    TABLE
  8  			      (CAST
  9  				 (MULTISET
 10  				   (SELECT LEVEL
 11  				    FROM   DUAL
 12  				    CONNECT BY LEVEL <= REGEXP_COUNT (combination, ',') + 1)
 13  			       AS SYS.ODCINUMBERLIST)))
 14  	     GROUP  BY combination, description) t1,
 15  	    (SELECT LISTAGG (vals, ',') WITHIN GROUP (ORDER BY vals) combo
 16  	     FROM   (SELECT REGEXP_SUBSTR (:example_string, '[^,]+', 1, LEVEL) vals
 17  		     FROM   DUAL
 18  		     CONNECT BY LEVEL <= REGEXP_COUNT (:example_string, ',') + 1)) t2
 19  WHERE  t1.combo = t2.combo
 20  /

COMBINATION                                                  DESCRIPTION
------------------------------------------------------------ ---------------
ART-001,ART-002,ART-003,ART-004                              SPR4
ART-004,ART-002,ART-001,ART-003                              SPR6

2 rows selected.

Report message to a moderator

Re: Need to match combination string at database [message #657508 is a reply to message #657506]

Fri, 11 November 2016 01:17

Shan.kumar
Messages: 5
Registered: November 2016

Junior Member

Hi Boehmer,

Thanks very much. This solution worked perfectly and that is exactly what I wanted.

Just one more help I need and after that I should be able to develop the entire system.

As a add on part of my system, I should be able to make different unique permutation of the materials. For that, I managed to get the below code and its working fine now. But problem is, I have to make the union list dynamic, as I can have different combination of materials,

with testdata as
(select 'ART-001' as col from dual
union
select 'ART-002' from dual
union
select 'ART-003' from dual
union
select 'ART-004' from dual
union
select 'ART-005' from dual
union
select 'ART-006' from dual),

-- create child column
testdata2 as
(select t.col as col1, t.col as col2 from testdata t)

select level, sys_connect_by_path(col1, ',') path
from testdata2 t
connect by prior col1 < col2
order by level, sys_connect_by_path(col1, ',');

Is there any way in this code, that I can put the material list in variable and send the variable to the code for permutation?

Thanks

[Updated on: Fri, 11 November 2016 06:02]

Report message to a moderator

Re: Need to match combination string at database [message #657515 is a reply to message #657508]

Fri, 11 November 2016 16:32

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_12.1.0.2.0> VARIABLE example_string VARCHAR2(50)
SCOTT@orcl_12.1.0.2.0> EXEC :example_string := 'ART-001,ART-002,ART-003,ART-004,ART-005,ART-006'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> COLUMN path FORMAT A60
SCOTT@orcl_12.1.0.2.0> WITH
  2    testdata AS
  3  	 (SELECT  REGEXP_SUBSTR (:example_string, '[^,]+', 1, LEVEL) AS col
  4  	  FROM	  DUAL
  5  	  CONNECT BY LEVEL <= REGEXP_COUNT (:example_string, ',') + 1),
  6    testdata2 AS
  7  	 (SELECT  col as col1, col as col2
  8  	  FROM	  testdata)
  9  SELECT  LEVEL, SYS_CONNECT_BY_PATH (col1, ',') path
 10  FROM    testdata2
 11  CONNECT BY PRIOR col1 < col2
 12  ORDER   BY LEVEL, path
 13  /

     LEVEL PATH
---------- ------------------------------------------------------------
         1 ,ART-001
         1 ,ART-002
         1 ,ART-003
         1 ,ART-004
         1 ,ART-005
         1 ,ART-006
         2 ,ART-001,ART-002
         2 ,ART-001,ART-003
         2 ,ART-001,ART-004
         2 ,ART-001,ART-005
         2 ,ART-001,ART-006
         2 ,ART-002,ART-003
         2 ,ART-002,ART-004
         2 ,ART-002,ART-005
         2 ,ART-002,ART-006
         2 ,ART-003,ART-004
         2 ,ART-003,ART-005
         2 ,ART-003,ART-006
         2 ,ART-004,ART-005
         2 ,ART-004,ART-006
         2 ,ART-005,ART-006
         3 ,ART-001,ART-002,ART-003
         3 ,ART-001,ART-002,ART-004
         3 ,ART-001,ART-002,ART-005
         3 ,ART-001,ART-002,ART-006
         3 ,ART-001,ART-003,ART-004
         3 ,ART-001,ART-003,ART-005
         3 ,ART-001,ART-003,ART-006
         3 ,ART-001,ART-004,ART-005
         3 ,ART-001,ART-004,ART-006
         3 ,ART-001,ART-005,ART-006
         3 ,ART-002,ART-003,ART-004
         3 ,ART-002,ART-003,ART-005
         3 ,ART-002,ART-003,ART-006
         3 ,ART-002,ART-004,ART-005
         3 ,ART-002,ART-004,ART-006
         3 ,ART-002,ART-005,ART-006
         3 ,ART-003,ART-004,ART-005
         3 ,ART-003,ART-004,ART-006
         3 ,ART-003,ART-005,ART-006
         3 ,ART-004,ART-005,ART-006
         4 ,ART-001,ART-002,ART-003,ART-004
         4 ,ART-001,ART-002,ART-003,ART-005
         4 ,ART-001,ART-002,ART-003,ART-006
         4 ,ART-001,ART-002,ART-004,ART-005
         4 ,ART-001,ART-002,ART-004,ART-006
         4 ,ART-001,ART-002,ART-005,ART-006
         4 ,ART-001,ART-003,ART-004,ART-005
         4 ,ART-001,ART-003,ART-004,ART-006
         4 ,ART-001,ART-003,ART-005,ART-006
         4 ,ART-001,ART-004,ART-005,ART-006
         4 ,ART-002,ART-003,ART-004,ART-005
         4 ,ART-002,ART-003,ART-004,ART-006
         4 ,ART-002,ART-003,ART-005,ART-006
         4 ,ART-002,ART-004,ART-005,ART-006
         4 ,ART-003,ART-004,ART-005,ART-006
         5 ,ART-001,ART-002,ART-003,ART-004,ART-005
         5 ,ART-001,ART-002,ART-003,ART-004,ART-006
         5 ,ART-001,ART-002,ART-003,ART-005,ART-006
         5 ,ART-001,ART-002,ART-004,ART-005,ART-006
         5 ,ART-001,ART-003,ART-004,ART-005,ART-006
         5 ,ART-002,ART-003,ART-004,ART-005,ART-006
         6 ,ART-001,ART-002,ART-003,ART-004,ART-005,ART-006

63 rows selected.

Report message to a moderator

Re: Need to match combination string at database [message #657554 is a reply to message #657515]

Mon, 14 November 2016 00:21

Shan.kumar
Messages: 5
Registered: November 2016

Junior Member

Hi Boehmer,

That worked. Thanks very much.

Report message to a moderator

Previous Topic:	PL/SQL Email
Next Topic:	Filling a table of object in function loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:06:43 CDT 2024