Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression : Find and Replace Specific String (Oracle 11g)
Regular Expression : Find and Replace Specific String [message #664684] Fri, 28 July 2017 06:39 Go to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Hi,
We have requirement where we need to build SQL query dynamically based on the scenario.
i.e COLUMN names of SELECT and GROUP BY will be replaced dynamically but problem is wherever there is alias name of column like 'AS column_name' in SELECT clause will not work if it is used in GROUP BY clause.

So could you please help in identifying and replacing 'AS column_name' from column_list variable which is of different format.

SELECT v_columns, COUNT(emp_name)
FROM employee
WHERE dept_id IN (10,20,30)
GROUP BY v_columns;

Scenario 1: v_columns -> emp_name, emp_doj, trunc((sal + 1000)/10) + 200 AS salary

Scenario 2: v_columns -> emp_name, emp_doj, trunc((sal + 1000)/10) + 200 AS salary, dept_name

Scenario 3: v_columns -> emp_name, emp_doj, trunc((sal + 1000)/10) + 200 
AS salary
, dept_name

Scenario 4: v_columns -> emp_name, trunc(sal + 1000) AS oldsalary, emp_doj, trunc((sal + 1000)/10) + 200 AS salary, dept_name

Thanks and Regards,
Lokesh
Re: Regular Expression : Find and Replace Specific String [message #664688 is a reply to message #664684] Fri, 28 July 2017 07:32 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
how are you building the dynamic queries in the first place?
Re: Regular Expression : Find and Replace Specific String [message #664689 is a reply to message #664688] Fri, 28 July 2017 07:35 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
If you are building the query dynamically then simply don't include the column alias in the query or don't use column alias

[Updated on: Fri, 28 July 2017 07:36]

Report message to a moderator

Re: Regular Expression : Find and Replace Specific String [message #664690 is a reply to message #664689] Fri, 28 July 2017 07:50 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks for your response.
Please ignore about building dynamic query which is lit bit complicated and involves business requirement.

Could you please help in finding and replacing 'AS column_name' in a given string ( for all 4 scenario mentioned above).

Thanks,
Lokesh
Re: Regular Expression : Find and Replace Specific String [message #664691 is a reply to message #664690] Fri, 28 July 2017 08:00 Go to previous messageGo to next message
BlackSwan
Messages: 25744
Registered: January 2009
Location: SoCal
Senior Member
write 4 static SELECT statements & invoke the one needed based upon input values
Re: Regular Expression : Find and Replace Specific String [message #664693 is a reply to message #664691] Fri, 28 July 2017 09:12 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks Blackswan for your response.
As I mentioned earlier column list will dynamically come so code should be able to consider all possible 4 scenario then identify and replace "AS COLUMN_LIST".
Re: Regular Expression : Find and Replace Specific String [message #664694 is a reply to message #664693] Fri, 28 July 2017 09:23 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
replace(lower(<dyanmic string>), ' as ', null)

Never mind.
You'll need to use instr to find the group by, another instr to find the subsequent as then substr to chop out the part of the string from the as to the following comma, or end of string.
It'll almost certainly be neater to fix while the string is being formed.

[Updated on: Fri, 28 July 2017 09:26]

Report message to a moderator

Re: Regular Expression : Find and Replace Specific String [message #664696 is a reply to message #664694] Fri, 28 July 2017 13:03 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks for your response.
But seems like I haven't explained requirement properly.
Here it is.

String: c1, c2 as c22, c3, c4 as c44, c5

After replacement of alias name.

String : c1, c2, c3, c4, c5

Note:
Number of columns and alias name of columns are not fixed.
Re: Regular Expression : Find and Replace Specific String [message #664697 is a reply to message #664696] Fri, 28 July 2017 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25744
Registered: January 2009
Location: SoCal
Senior Member
EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.
Re: Regular Expression : Find and Replace Specific String [message #664703 is a reply to message #664697] Sat, 29 July 2017 08:50 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Thanks for your response.
But it won't work for my requirement.

I'm looking for regular expression which reads string of column names and then identify column having alias name ( column-n as column_t) and then just remove "AS column_t" so that output would be list of column names without alias name.

I hope it is clear.
Please let me know if require more info.

Thanks.
Re: Regular Expression : Find and Replace Specific String [message #664704 is a reply to message #664703] Sat, 29 July 2017 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 25744
Registered: January 2009
Location: SoCal
Senior Member
>I hope it is clear.
It is clear that you don't understand how Oracle & SQL work.

SQL statement must be known & fixed at compile time.
To "construct" SQL statement on the fly, EXECUTE IMMEDIATE must be (ab)used.
Re: Regular Expression : Find and Replace Specific String [message #664705 is a reply to message #664704] Sat, 29 July 2017 09:32 Go to previous messageGo to next message
lokimisc
Messages: 98
Registered: February 2008
Member
Blackswan I do understand how SQL works.. Smile but I was trying to simplify question I know I need to use execute immediate to execute sql.

Requirement is Column list which are used in SELECT CLAUSE will be used in GROUP BY CLAUSE also so any possible occurrence of alias name in column list has to be removed so that SQL works.

Note: list of column names will be coming dynamically and it is not fixed column names.


Thanks.
Re: Regular Expression : Find and Replace Specific String [message #664714 is a reply to message #664705] Mon, 31 July 2017 02:05 Go to previous messageGo to next message
flyboy
Messages: 1850
Registered: November 2006
Senior Member
lokimisc wrote on Sat, 29 July 2017 16:32
Note: list of column names will be coming dynamically and it is not fixed column names.
Fine. So, after you will somehow construct a correct statement (probably REGEXP_REPLACE or SUBSTR/INSTR can achieve it), you will probably be interested in fetching its result set.
As you know SQL, you surely know that (when using EXECUTE IMMEDIATE) you need a static structure for storing it. Fail.
So, you probably follow in exploring other way of dynamic call (DBMS_SQL) and finally end up with a dynamic structure(s) full of dynamic data.
Now ... what? Which "generic" action are you planning to perform on that data?
Just for completeness, here is an example of PRINT_TABLE function doing just that (store it into DBMS_OUTPUT buffer) in this AskTom thread: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
Good luck.
Re: Regular Expression : Find and Replace Specific String [message #664715 is a reply to message #664696] Mon, 31 July 2017 03:05 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
lokimisc wrote on Fri, 28 July 2017 19:03
Thanks for your response.
But seems like I haven't explained requirement properly.
I did understand, once I switched my brain. Ignore the bit I greyed out, that was my original response before I thought about it.
Re: Regular Expression : Find and Replace Specific String [message #664720 is a reply to message #664684] Mon, 31 July 2017 20:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8778
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
  2    (v_ref	  in out sys_refcursor,
  3  	v_columns in	 varchar2)
  4  as
  5    v_sql		 varchar2(32767);
  6  begin
  7    v_sql :=
  8  	 'select ' || v_columns || ', count(ename)
  9  	  from	 emp
 10  	  where  deptno in (10,20,30)
 11  	  group  by ' || RTRIM (REGEXP_REPLACE (UPPER (V_COLUMNS) || ',', '\s+AS\s+\w+\s*,', ','), ',') ||
 12  	' order by ' || RTRIM (REGEXP_REPLACE (UPPER (V_COLUMNS) || ',', '\s+AS\s+\w+\s*,', ','), ',');
 13    dbms_output.put_line (v_sql);
 14    open v_ref for v_sql;
 15  end test_proc;
 16  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable g_ref refcursor
SCOTT@orcl_12.1.0.2.0> exec test_proc (:g_ref, 'deptno as dept_name, job as job_name')
select deptno as dept_name, job as job_name, count(ename)
     from   emp

where  deptno in (10,20,30)
     group  by DEPTNO, JOB order by DEPTNO, JOB

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> print g_ref

 DEPT_NAME JOB_NAME  COUNT(ENAME)
---------- --------- ------------
        10 CLERK                1
        10 MANAGER              1
        10 PRESIDENT            1
        20 ANALYST              2
        20 CLERK                2
        20 MANAGER              1
        30 CLERK                1
        30 MANAGER              1
        30 SALESMAN             4

9 rows selected.
Re: Regular Expression : Find and Replace Specific String [message #664733 is a reply to message #664720] Tue, 01 August 2017 07:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara,

Just one note - that regexp pattern will not work for quoted aliases:

SELECT  RTRIM(REGEXP_REPLACE(UPPER('deptno as dept_name, job as "job_name"') || ',','\s+AS\s+\w+\s*,', ','),',')
  FROM  DUAL
/

RTRIM(REGEXP_REPLACE(UPPE
-------------------------
DEPTNO, JOB AS "JOB_NAME"

SQL> 

SY.
Re: Regular Expression : Find and Replace Specific String [message #664743 is a reply to message #664733] Tue, 01 August 2017 11:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8778
Registered: November 2002
Location: California, USA
Senior Member
Solomon Yakobson wrote on Tue, 01 August 2017 05:42
Barbara,

Just one note - that regexp pattern will not work for quoted aliases:

SELECT  RTRIM(REGEXP_REPLACE(UPPER('deptno as dept_name, job as "job_name"') || ',','\s+AS\s+\w+\s*,', ','),',')
  FROM  DUAL
/

RTRIM(REGEXP_REPLACE(UPPE
-------------------------
DEPTNO, JOB AS "JOB_NAME"

SQL> 

SY.

Good point. The following revision allows for quoted aliases, including those containing spaces, and preserves the case of the column names in case they are quoted. There may be other things that I have not thought of.

SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
  2    (v_ref	  in out sys_refcursor,
  3  	v_columns in	 varchar2)
  4  as
  5    v_sql		 varchar2(32767);
  6  begin
  7    v_sql :=
  8  	 'select ' || v_columns || ', count(ename)
  9  	  from	 emp
 10  	  where  deptno in (10,20,30)
 11  	  group  by ' || RTRIM (REGEXP_REPLACE (V_COLUMNS || ',', '\s+AS\s+"?(\w|\s)+"?\s*,', ',', 1, 0, 'i'), ',') ||
 12  	' order  by ' || RTRIM (REGEXP_REPLACE (V_COLUMNS || ',', '\s+AS\s+"?(\w|\s)+"?\s*,', ',', 1, 0, 'i'), ',');
 13    dbms_output.put_line (v_sql);
 14    open v_ref for v_sql;
 15  end test_proc;
 16  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable g_ref refcursor
SCOTT@orcl_12.1.0.2.0> exec test_proc (:g_ref, 'deptno as dept_name, job as "job_name"')
select deptno as dept_name, job as "job_name", count(ename)
     from   emp

where  deptno in (10,20,30)
     group  by deptno, job order  by deptno, job

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> print g_ref

 DEPT_NAME job_name  COUNT(ENAME)
---------- --------- ------------
        10 CLERK                1
        10 MANAGER              1
        10 PRESIDENT            1
        20 ANALYST              2
        20 CLERK                2
        20 MANAGER              1
        30 CLERK                1
        30 MANAGER              1
        30 SALESMAN             4

9 rows selected.
Re: Regular Expression : Find and Replace Specific String [message #664748 is a reply to message #664743] Tue, 01 August 2017 13:38 Go to previous message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara, quoting aliases isn't always done to preserve case - alias can have characters not allowed in unquoted names:

SELECT  RTRIM(REGEXP_REPLACE('deptno as dept_name, job as "title (job name)"' || ',','\s+AS\s+"?(\w|\s)+"?\s*,',',',1,0,'i'),',')
  FROM  DUAL
/

RTRIM(REGEXP_REPLACE('DEPTNOASDEP
---------------------------------
deptno, job as "title (job name)"

SQL> 

It would be better to simply use ([^"]) rather than (\w|\s). This will cover any alias that doesn't contain double quote as part of the alias:

SELECT  RTRIM(REGEXP_REPLACE('deptno as dept_name, job as "title (job name)"' || ',','\s+AS\s+"?([^"])+"?\s*,',',',1,0,'i'),',')
  FROM  DUAL
/

RTRIM(REGEX
-----------
deptno, job

SQL> 

SY.

[Updated on: Tue, 01 August 2017 13:49]

Report message to a moderator

Previous Topic: string aggregation
Next Topic: string operations
Goto Forum:
  


Current Time: Tue Dec 12 12:56:07 CST 2017

Total time taken to generate the page: 0.01955 seconds