Home » SQL & PL/SQL » SQL & PL/SQL » SQL -Report missing string when running a query (Oracle 11 windows 10)
SQL -Report missing string when running a query [message #685605] Wed, 16 February 2022 14:35 Go to next message
Kevin1967
Messages: 2
Registered: February 2022
Junior Member
I am new to sql and this forum, looking for some help.
Thank you for your cooperation

Table name Group
Column - name

SBM-TRANS-ROSE Exective Staff | MCB-FRAM-ROUG Personnel executif
ATM-CPES-ROSE Mgt | VAC-FRAM-VERT Gestion
SBM-TRANS-ROSE-MANG TSFD | MCB-FRAM-ROUG-TOMG ESAN
SBM-TRANS-ROSE-MANG OT | MCB-FRAM-ROUG-TOM LAX

Required Output

SBM-TRANS-ROSE | MCB-FRAM-ROUG
ATM-CPES-ROSE | VAC-FRAM-VERT
SBM-TRANS-ROSE-MANG-TSFD | MCB-FRAM-ROUG-TOMG-ESAN
SBM-TRANS-ROSE-MANG-OT | MCB-FRAM-ROUG-TOM-LAX




Select G.name,
SUBSTR(G.NAME,1,INSTR(G.NAME,' ')-1) || ' | ' || SUBSTR(SUBSTR(G.NAME,INSTR(G.NAME,' | ',-3)+3),1,INSTR(SUBSTR(G.NAME,INSTR(G.NAME,' | ',-3)+3),' ')-1) as txt4
from Group G




My issue when I run the query I got the following ,
SBM-TRANS-ROSE | MCB-FRAM-ROUG
ATM-CPES-ROSE | VAC-FRAM-VERT
SBM-TRANS-ROSE-MANG | MCB-FRAM-ROUG-TOMG
SBM-TRANS-ROSE-MANG | MCB-FRAM-ROUG-TOM

I am missing TSFD and ESAN in the third row
OT and LAX in the fourth row




Re: SQL -Report missing string when running a query [message #685606 is a reply to message #685605] Thu, 17 February 2022 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68217
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 100 characters when you format.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Thu, 17 February 2022 00:20]

Report message to a moderator

Re: SQL -Report missing string when running a query [message #685609 is a reply to message #685606] Thu, 17 February 2022 03:40 Go to previous messageGo to next message
piripicchio
Messages: 18
Registered: April 2018
Location: Rome
Junior Member
Try using regular expressions:

SQL> WITH "GROUP"
  2  AS 
  3    (SELECT COLUMN_VALUE NAME
  4    FROM TABLE (SYS.ODCIVARCHAR2LIST('SBM-TRANS-ROSE Exective Staff | MCB-FRAM-ROUG Personnel executif', 'SBM-TRANS-ROSE-MANG OT | MCB-FRAM-ROUG-TOM LAX', 'ATM-CPES-ROSE Mgt | VAC-FRAM-VERT Gestion', 'SBM-TRANS-ROSE-MANG TSFD | MCB-FRAM-ROUG-TOMG ESAN')))
  5  SELECT NAME, REGEXP_REPLACE(REGEXP_REPLACE(NAME, '[[:space:]][[:upper:]]*[[:lower:]]+', NULL), '([[:upper:]])([[:space:]])([[:upper:]])', '\1-\3') FORMATTED_NAME FROM "GROUP";

NAME                                                                             FORMATTED_NAME                                    
-------------------------------------------------------------------------------- --------------------------------------------------
SBM-TRANS-ROSE Exective Staff | MCB-FRAM-ROUG Personnel executif                 SBM-TRANS-ROSE | MCB-FRAM-ROUG                    
SBM-TRANS-ROSE-MANG OT | MCB-FRAM-ROUG-TOM LAX                                   SBM-TRANS-ROSE-MANG-OT | MCB-FRAM-ROUG-TOM-LAX    
ATM-CPES-ROSE Mgt | VAC-FRAM-VERT Gestion                                        ATM-CPES-ROSE | VAC-FRAM-VERT                     
SBM-TRANS-ROSE-MANG TSFD | MCB-FRAM-ROUG-TOMG ESAN                               SBM-TRANS-ROSE-MANG-TSFD | MCB-FRAM-ROUG-TOMG-ESAN

Re: SQL -Report missing string when running a query [message #685619 is a reply to message #685609] Fri, 18 February 2022 15:04 Go to previous messageGo to next message
Kevin1967
Messages: 2
Registered: February 2022
Junior Member
Thank you so much Piripicchio, it works but i have some issues with some records, it seems numbers and special characters are having issue. Any idea how to remove it

SBM-TRANS-ROSE-MANG TSFD Tech 4 | MCB-FRAM-ROUG-TOMG ESAN Techniciens de niveau 4 I am getting SBM-TRANS-ROSE-MANG TSFD 4 | MCB-FRAM-ROUG-TOMG ESAN 4 instead SBM-TRANS-ROSE-MANG TSFD | MCB-FRAM-ROUG-TOMG ESAN
SBM-TRANS-ROSE-MANG OT Team Leader | MCB-FRAM-ROUG-TOM LAX Chef d’équipe this one i am getting SBM-TRANS-ROSE-MANG-OT | MCB-FRAM-ROUG-TOM-LAXéquipe
Re: SQL -Report missing string when running a query [message #685622 is a reply to message #685619] Sat, 19 February 2022 00:26 Go to previous message
Michel Cadot
Messages: 68217
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

  • We all saw you want to concatenate the first part with sometimes something from the second part. To avoid many round trips, tell us when and what has to be concatenated and when it must not.
  • To help us to help you provide a test case, as complete as possible to covert all the cases you identified in first point
  • Show you respect the work people do here to help others and format your post as explained in How to use [code] tags and make your code easier to read
Please post what is requested in the way it is requested.

[Updated on: Sat, 19 February 2022 01:10]

Report message to a moderator

Previous Topic: Facing issie in 19c
Next Topic: Getting ORA-24374 , when using function in with query (merged)
Goto Forum:
  


Current Time: Thu Jun 30 13:56:54 CDT 2022