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  |
lokimisc
Messages: 101
Registered: February 2008
|
Senior 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 #664703 is a reply to message #664697] |
Sat, 29 July 2017 08:50  |
lokimisc
Messages: 101
Registered: February 2008
|
Senior 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 #664714 is a reply to message #664705] |
Mon, 31 July 2017 02:05 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
lokimisc wrote on Sat, 29 July 2017 16:32Note: 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 #664720 is a reply to message #664684] |
Mon, 31 July 2017 20:22 |
 |
Barbara Boehmer
Messages: 9088
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 |
Solomon Yakobson
Messages: 3273
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 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Solomon Yakobson wrote on Tue, 01 August 2017 05:42Barbara,
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 |
Solomon Yakobson
Messages: 3273
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 |
|
|
|
Goto Forum:
Current Time: Fri Apr 19 05:12:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
but I was trying to simplify question I know I need to use execute immediate to execute sql.