Reverse in Oracle this path z/y/x to x/y/z
The simplest way would probably be to write a stored pl/sql function, however it can be done with SQL (Oracle) alone.
This will decompose the path in subpath:
SQL> variable path varchar2(4000);
SQL> exec :path := 'a/b/c/def';
PL/SQL procedure successfully completed
SQL> SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path, ROWNUM rk
2 FROM dual
3 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1;
SUB_P RK
----- --
a 1
b 2
c 3
def 4
We then recompose the reversed path with the sys_connect_by_path
:
SQL> SELECT MAX(sys_connect_by_path(sub_path, '/')) reversed_path
2 FROM (SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path,
3 ROWNUM rk
4 FROM dual
5 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1)
6 CONNECT BY PRIOR rk = rk + 1
7 START WITH rk = length(regexp_replace(:path, '[^/]', '')) + 1;
REVERSED_PATH
-------------
/def/c/b/a
Get Index of number in reverse direction of a string - PLSQL
You can try:
select regexp_instr(reverse('CUSTOM123XYZ'), '[[:digit:]]',1,1) from dual
Output: 4
Zero based index would be:
select regexp_instr(reverse('CUSTOM123XYZ'), '[[:digit:]]',1,1)-1 from dual
Output: 3
If you want the rest of the string from the last number, you can use substr and take advantage of the negative position to count from end of string:
select substr('CUSTOM123XYZ', -1 * (regexp_instr(reverse('CUSTOM123XYZ'), '[[:digit:]]',1,1)-1)) from dual;
Output: XYZ
An example testing multiple input strings:
with d as (
select 'CUSTOM123XYZ' as input_str from dual
union
select 'CUSTOM123XZ' as input_str from dual
union
select 'CUSTOM 1 X 3YZ' as input_str from dual
)
select input_str,
substr(input_str, -1 * (regexp_instr(reverse(input_str), '[[:digit:]]',1,1)-1)) as result
from d
Output:
INPUT_STR RESULT
CUSTOM 1 X 3YZ YZ
CUSTOM123XYZ XYZ
CUSTOM123XZ XZ
Remove reverse duplicates from an SQL query
First of all, welcome to 2012. We have migrated away from relating tables using commas. It was introdued in ANSI 89 but is severely lacking. Nowaways, the correct way is to write queries using the ANSI 92/99/2003 JOIN syntax.
The solution to your problem is to turn your bidirectional inequality <>
into a unidirectional inequality, either <
or >
whichever you prefer.
select e.column3, f.column3
from example as e
join example as f on e.column2 = f.column2 and e.column3 < f.column3
Oracle- create a temporary resultset for use in a query
If you are using oracle 11g you can do this
with t as
(
select (column_value).getnumberval() Codes from xmltable('1,2,3,4,5')
)
SELECT * FROM t
WHERE NOT EXISTS (SELECT 1 FROM M_ITEMS M WHERE codes = M.ITEM_CODE);
or
with t as
(
select (column_value).getstringval() Codes from xmltable('"A","B","C"')
)
SELECT * FROM t
WHERE NOT EXISTS (SELECT 1 FROM M_ITEMS M WHERE codes = M.ITEM_CODE);
Which is better for PL/SQL, IF-ELSE OR SELECT DECODE IN ORACLE
In Oracle PLSQL
block , 2 types of engines works. First SQL
engine and another PLSQL
engine. Whenever you write a SQL
statement in a PLSQL
block, the switching of engine takes place and this phenomena is called Context
Switching. The more context switching the less performant application would be.
When you do :
if var1 = 'a' then
var2 := 'x';
elseif var1 = 'b' then
var2 := 'y';
else
var2 := 'z';
end if;
The statement is evaluated in PLSQL
engine and no context switching occurs. But when you do :
begin
select decode(var1,'a','x','b','y','z') into var2 from dual;
end;
PLSQL
engine changes the control to SQL engine and context switching takes places. So this operation would make less performant.
Directed graph in Oracle SQL using recursive query visiting each node only once
For keeping the traversing algorithm out of returning to already visited edges, one can indeed keep the visited edges somewhere. As you already found out, you won't get much of a success with a string concatenation. However, there are other usable "value concatenation" techniques available...
You have to have one handy schema-level collection of scalars at your disposal:
create or replace type arr_strings is table of varchar2(64);
And then you can collect the visited edges to that collection in each iteration:
with nondirected$ as (
select from_id, to_id, from_id||'-'||to_id as edge_desc
from edge
where from_id != to_id
union all
select to_id, from_id, from_id||'-'||to_id as edge_desc
from edge
where (to_id, from_id) not in (
select from_id, to_id
from edge
)
),
graph$(lvl, from_id, to_id, edge_desc, visited_edges) as (
select 1, from_id, to_id, edge_desc,
arr_strings(edge_desc)
from nondirected$ R
where from_id in (&nodes)
--
union all
--
select
lvl+1,
Y.from_id, Y.to_id, Y.edge_desc,
X.visited_edges multiset union arr_strings(Y.edge_desc)
from graph$ X
join nondirected$ Y
on Y.from_id = X.to_id
where not exists (
select 1
from table(X.visited_edges) Z
where Y.edge_desc = Z.column_value
)
)
search breadth first by edge_desc set order_id
cycle edge_desc set is_cycle to 1 default 0,
ranked_graph$ as (
select C.*,
row_number() over (partition by edge_desc order by lvl, order_id) as rank$
from graph$ C
-- where is_cycle = 0
)
select *
from ranked_graph$
--where rank$ <= 1
order by lvl, order_id
;
Notes
- I pre-processed the directed graph to a nondirected one by
union
-ing a set of reverse edges to the input. That should make the recursive traversal predicates easier to read. Solely for my purposes of easier reading+writing of the SQL. You don't have to do that, of course. - I remember trying something like this a few years ago on Oracle 11.2. And I remember that it was failing, though I don't remember why. On 12.2, it ran OK. Just give it a try on 11g, too; I don't have one available.
- Since each iteration does, in addition to the traversal inner join, also an anti-join, I sincerely doubt that this is going to be more performant. However, it for sure solves the problem of lowering the number of recursive nestings.
- You'll have to resolve the desired ordering on your own, as you probably understood from my comments. :-)
Limiting the revisited edges to zero
In SQL, you can't. The PostgreSQL solution you mentioned does do it. In Oracle, however, you can't. You would have to, for each traversal join, test rows for all other traversal joins. And that would mean some kind of aggregation or analytics... which Oracle forbids and throws out an ORA exception.
PLSQL to the rescue?
You can do it in PL/SQL, though. How much performant it is supposed to be, depends on how much memory you want to spend on prefetching edges from DB vs. how many SQL roundtrips you are willing to take to traverse the graph from "current" nodes or if you are willing to use even more memory to keep the visited nodes in a fancy indexed-by-edge collection vs. if you rather anti-join against a regular arr_output
collection l_visited_nodes
. You have multiple choices, choose wisely.
Anyway, for the simplest scenario with heavier use of SQL engine, this might be the code you're looking for...
create or replace
package pkg_so_recursive_traversal
is
type rec_output is record (
from_id edge.from_id%type,
to_id edge.to_id%type,
lvl integer
);
type arr_output is table of rec_output;
function traverse_a_graph
( i_from in arr_strings
, i_is_directed in varchar2 default 'NO' )
return arr_output
pipelined;
end pkg_so_recursive_traversal;
/
create or replace
package body pkg_so_recursive_traversal
is
function traverse_a_graph
( i_from in arr_strings
, i_is_directed in varchar2 )
return arr_output
pipelined
is
l_next_edges arr_output;
l_current_edges arr_output;
l_visited_edges arr_output := arr_output();
l_out rec_output;
i pls_integer;
l_is_directed varchar2(32) := case when i_is_directed = 'YES' then 'YES' else 'NO' end;
begin
select E.from_id, E.to_id, 0
bulk collect into l_next_edges
from table(i_from) F
join edge E
on F.column_value in (E.from_id, case when l_is_directed = 'YES' then null else E.to_id end)
where E.from_id != E.to_id;
l_out.lvl := 0;
loop
dbms_output.put_line(l_next_edges.count());
exit when l_next_edges.count() <= 0;
l_out.lvl := l_out.lvl + 1;
-- spool the edges to output
i := l_next_edges.first();
while i is not null loop
l_out.from_id := l_next_edges(i).from_id;
l_out.to_id := l_next_edges(i).to_id;
pipe row(l_out);
i := l_next_edges.next(i);
end loop;
l_current_edges := l_next_edges;
l_visited_edges := l_visited_edges multiset union l_current_edges;
-- find next edges
select unique E.from_id, E.to_id, 0
bulk collect into l_next_edges
from table(l_current_edges) CE
join edge E
on CE.to_id in (E.from_id, case when l_is_directed = 'YES' then null else E.to_id end)
or l_is_directed = 'NO' and CE.from_id in (E.from_id, E.to_id)
where E.from_id != E.to_id
and not exists (
select 1
from table(l_visited_edges) VE
where VE.from_id = E.from_id
and VE.to_id = E.to_id
);
end loop;
return;
end;
end pkg_so_recursive_traversal;
/
When called for the starting node of A
and considering the graph to be undirected...
select *
from table(pkg_so_recursive_traversal.traverse_a_graph(
i_from => arr_strings('A'),
i_is_directed => 'NO'
));
... it yields...
FROM_ID TO_ID LVL
---------- ---------- ----------
A B 1
C A 1
C E 2
B D 2
C F 2
E B 2
G D 3
H F 3
G I 4
Notes
- Again, I did not put any effort to keep the ordering you requested, as you said it's not that important.
- This is doing multiple (exactly 5 for your example inputs) SQL roundtrips to the
edge
table. That may or may not be a bigger performance hit when compared to the pure-SQL solution with redundant edge visiting. Test more solutions properly, see which one works for you the best. - This particular piece of code will work on 12c and higher. For 11g and lower you'll have to declare the
rec_output
andarr_output
types on the schema level.
Related Topics
Why When Matched' Cannot Appear More Than Once in a 'Update' Clause of a Merge Statement
What Is the Most Elegant Way to Store Timestamp with Nanosec in Postgresql
Join Tables on Nearest Date in the Past, in MySQL
Connect to Remote SQL Database Using Excel
Months Between Two Dates in SQL Server with Starting and End Date of Each of Them in SQL Server
SQL Trigger After Insert Update Another Table with Conditions
Can SQL Server Pivot Without Knowing the Resulting Column Names
Mysql: Name Primary Key in Create Table Statement
T-SQL How to Convert Comma Separated String of Numbers to Integer
Insert Blank Row Between Groups of Rows and Sorted by Id in SQL
Support for JSON in Oracle 11G
SQL Script to Change All Table References in All Stored Procedures
Why Isn't Postgres Using the Index
Count Values for Every Column in a Table
Calculate Difference Between Start_Time and End_Time in Seconds from Unix_Time Yyyy-Mm-Dd Hh:Mm:Ss
How to Create a Check Constraint on a Varchar Column in SQL Server Specifying a Minimum Data Length