I have a like below data : so i wanted to remove all the value after = and before | except for 1234. expected data : Answer You can use: Which, for the sample data: Outputs: VALUE 1234=A||1456=||1789= 1245=||1234=V 1234,1133 1456=||1234=1,234||1234 sqlfiddle here
Tag: oracle
XMLTable query returns no result
I’m only passingly familiar with XML. I need to parse a response from a SOAP request. From a lot of searching, I’ve developed the following query to try to extract the status. Ultimately, I’d like to get the status, cntr and cntr_status fields from the response. My query gives no error, but …
Oracle select column names dynamically from another table
I have two tables. Lets say tab1 and tab2. tab1 has data like this: ID Field Name 00001 col1 00001 col63 00002 col3 00002 col47 00003 col22 00003 col40 Each ID has always 2 Field Name values. And tab2 has columns col1,col2,col3,col4….col70 I want to select columns dynamically from tab2. Like for 00001 i…
SQL lag function avoid getting first row as null
I have a sql query if my filter date is > 20210625 , I get value as 27 and previous value as null , is it possible to get the prev_value 25. If there is no filter I will get it. But filter is required to prevent reading the whole table. Also one more requirement. If previous value is null
Oracle SQL: Case statement
I am not sure where to put the case statement whether in select or where clause. I am trying to get data from table c when cnt_subj = MEPC_PL then metric_id will be equal to 6135 from table m. Can anyone help? Thank you in advance! 🙂 Answer I think this is the query you are looking for: Do you
Oracle SQL hierarchical query from bottom to top
I have a table where I want to go from bottom to top using hierarchical queries. The problem is that I need the get the value of one column from root (top) using CONNECT_BY_ROOT, but since I reverse the way the hierarchical query works (reverse the prior in connect by and the start with), this function (CONNE…
ORACLE json_object_t – error when call get_string() method on object got by chain of methods .get_array() .get()
I am trying to get a value from an array contained in JSON-object by chain of methods of JSON_OBJECT_T type – .get_array(…).get(0).get_string(…) But getting an exception component ‘GET_STRING’ must be declared Here is my code, could someone explain why the chain does not work? An…
Why ‘ in ‘ is so much faster than ‘ = ‘ in SQL Select?
This select’s execution time is approximately 25 ~ 30 sec. Execution plan for ‘ = ‘: But if I change ‘ = ‘ for ‘ in ‘, then it becomes so much faster about 0.040 ~ 0.060-sec average. Execution plan for ‘ in ‘: And there have been opposite cases like this, …
How to generate INSERT Statements with Subqueries in Oracle SQL Developer?
I need to move some data from Environment A to Environment B. So far, so easy. But some of the columns have FK Constraints and unfortunately the lookup data is already on Environment B and has different PKs. Lucky me, there are other unique columns I could do a mapping on. Therefore I wonder if SQL Developer …
Merge two row in one from different table
Below is simple example, I need in one row display value from different table. eg. need resaults as: Answer With sample data you posted, that’s join with coalesce.