Below is a text in a column: P348955: REMOVE NHA P210 PANEL PN : 6703ELM21 & REMOVE SUB ASSY PN: ELM2000-10-10 (ITEM 70) QTY 2EA & PN: ELM200-11-10 OR ALT (ITEM 75) QTY 1EA & PN ELM1057-1 QTY 1EA REQUESTED BY MMEECHAN REF ID 7369 I am looking to extract all similar patterns from that text, The pat…
Tag: oracle
How SQL can help to get required table format?
I have a query to know about valid and invalid database objects. I am using this query: SELECT object_type,Status, count(*) FROM all_objects where owner = ‘SchemaName’ group by object_type,…
SQL Select – Get a count of data as a separate column error
I am working on this query whereas I have a table as follows Code | Date ————- 001 | 20-JUN 001 | 20-JUN 002 | 20-JUN 003 | 20-JUN 002 | 20-JUN 001 | 20-JUN 002 | …
find only the duplicate values in listagg
I am having some duplicate rows in my table. I wanted to find those duplicates. After i have done listagg on the column rp_num my table will have duplicate value like this : SELECT distinct vt….
Oracle Optimize Query
i’m working with oracle pl/sql and i have a stored procedure with this query, and it is a bit convoluted, but it gets the job done, the thing is it takes like 35 minutes, and the sql developer Autotrace says that is doing a full scan even though the tables have their indexes. So is there any way to impr…
Extracting text between a character and space
I have this text and I want to extract the id of collector that is bolded knowing that the id is not fixed it May 5 digits or even three digits EX: what is the best approach to do that by oracle functions Answer I think you need REGEXP_SUBSTR as follows:
Query with empty or one row only
I’m using an oracle DB and I need to know if certain value is on myTable (it can be 0-N times), but i’m using a software that does some tasks with each row of the query output, so, my result should be empty when I have no value in myTable (so the software will do nothing) or one row when
In Oracle, Can you create a new table with the same groups if you use CTAS Query?
I use the query CTAS to create a new table, however, when CTAS has finished, other users can´t select the new table, but they had access to the old, Is it a way to pass all the users and groups to the …
Problem in using group by statement in sql
I am not able to group by table which has all the values same but has two rows. I am not understanding what is the reason behind that. After running this query i am getting the table below and i want to group the table along the LOT_No. TABLE : Now when i am trying to Group by LOT_No, i
Select count(*) query from JdbcTemplate returns different value than running the query from SQL terminal
I have this simple query : called from Spring Boot. There are no rows with the ID (150) passed in. When running queryForObject, variable count comes up as 1 , while when running the same query from SQL Developer it comes up as 0!! What’s going on here? What am I missing? Answer Possible causes a) you ar…