Home » SQL & PL/SQL » SQL & PL/SQL » Extract words from a string and use them as variables (oracle)
Extract words from a string and use them as variables [message #682742] Fri, 13 November 2020 10:45 Go to next message
Haykel_bh
Messages: 5
Registered: November 2020
Junior Member
Hi,

Is it possible to extract words from a string and use them as variables?

Example:

DECLARED
    - I want to extract recunit_a and recunit_b and use them as variables afterwards.
   vv_where_clause VARCHAR2 (1000): = 'recunit_a = rec_unit_b';
   BEGIN
      recunit_a: = 10;
      rec_unit_b: = 20;
   END;
Re: Extract words from a string and use them as variables [message #682743 is a reply to message #682742] Fri, 13 November 2020 12:43 Go to previous messageGo to next message
EdStevens
Messages: 1293
Registered: September 2013
Senior Member
Haykel_bh wrote on Fri, 13 November 2020 10:45
Hi,

Is it possible to extract words from a string and use them as variables?

Example:

DECLARED
    - I want to extract recunit_a and recunit_b and use them as variables afterwards.
   vv_where_clause VARCHAR2 (1000): = 'recunit_a = rec_unit_b';
   BEGIN
      recunit_a: = 10;
      rec_unit_b: = 20;
   END;
The simple answer to your exact question "extract words from a string and use them (assign to) variables is 'yes'. Typically you would use 'regular expressions' to do this. Perhaps instead of regular expressions, a simple instr() function, though that is less flexible.

But your presented code strongly suggests there is more to your question. It looks like you want to assign the text of a WHERE clause to a string, and then use that in a sql statement. If that is what you want, then you will have to construct your sql statement as a variable string, then use dynamic sql to execute it. And in what appears to be your case, that is probably a bad idea.

Also, your syntax for assigning a value to a variable is wrong. In pl/sql, the assignment operator is ':=' a colon followed by an equal sign, with no intervening space.

All in all, you appear to be asking a classic 'x-y question'.
Re: Extract words from a string and use them as variables [message #682744 is a reply to message #682743] Fri, 13 November 2020 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

+1

Re: Extract words from a string and use them as variables [message #682745 is a reply to message #682744] Fri, 13 November 2020 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And as I said in your previous topic:

Michel Cadot wrote on Wed, 04 November 2020 16:46

In this case you have to provide a test case: CREATE TABLE and INSERT statements for some data so we can reproduce what you have in different versions to know if it is a bug in one of these versions.

You have to provide the complete version numbers also for your 18c version (query v$version and/or provide the db message when you connect with SQL*Plus).
Re: Extract words from a string and use them as variables [message #682746 is a reply to message #682743] Fri, 13 November 2020 15:01 Go to previous messageGo to next message
Haykel_bh
Messages: 5
Registered: November 2020
Junior Member

I have a set of words separated by a separator ('=')
After extracting these words, I want to declare variables in the names of these words.

vv_where_clause VARCHAR2 (1000): = 'recunit_a = rec_unit_b';
recunit_a NUMBER: = 10;
recunit_b NUMBER: = 20;


vv_where_clause VARCHAR2 (1000): = 'A = B';
A NUMBER := 5;
B NUMBER := 15;
Re: Extract words from a string and use them as variables [message #682747 is a reply to message #682746] Sat, 14 November 2020 00:24 Go to previous message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ed already answered to this.

EdStevens wrote on Fri, 13 November 2020 19:43
...
All in all, you appear to be asking a classic 'x-y question'.

[Updated on: Sat, 14 November 2020 00:27]

Report message to a moderator

Previous Topic: table refresh process - discuss optoins
Next Topic: Date to Jewish/Hebrew Date (2 merged)
Goto Forum:
  


Current Time: Fri Jan 22 09:36:19 CST 2021