SQL: Substring/Trim column values based on specific word

Question

I have table A. How do I trim the column COND so that the values keeps the word ITEM and everything after? I want the results to look like Table B:

Table A

|ID   |                        COND                         |
|:--- |:----------------------------------------------------|
|1    |ITEM = 'B967','B677','O342'                          |
|2    |ITEM = 'J833','UR09','ILY7'                          |
|3    |ITEM = 'B967, OJ87'                                  |
|4    |(CATE = 'OBRT', 'TMPT') AND (ITEM = 'UR09','ILY7')   |
|5    |CATE='ONTE,PNTE,QSDW,RVCG' OR ITEM=J833              |

Table B

|ID   |             COND          |
|:--- |:--------------------------|
|1    |ITEM = 'B967','B677','O342'|
|2    |ITEM = 'J833','UR09','ILY7'|
|3    |ITEM = 'B967, OJ87'        |
|4    |ITEM = 'UR09','ILY7')      |
|5    |ITEM=J833                  |

You don't want that closing paren for ID 4 do you?

Gary_W
– Gary_W

2022-08-25 14:44:41 +00:00
Commented Aug 25, 2022 at 14:44 — Gary_W
– Gary_W, Commented Aug 25, 2022 at 14:44
It would be nice if that was removed in the final table too

NEWBIE
– NEWBIE

2022-08-25 15:55:54 +00:00
Commented Aug 25, 2022 at 15:55 — NEWBIE
– NEWBIE, Commented Aug 25, 2022 at 15:55

Isolated · Accepted Answer · 2022-08-22 18:46:03Z

1

You can use a combination of substring and instring. INSTR will return the position where "ITEM" begins, and then SUBSTR will return everything after that position start.

select id, cond, SUBSTR(cond, INSTR(cond, 'ITEM', 1)) as col_name
from my_table

Output:

ID	COND	COL_NAME
1	ITEM = 'B967','B677','O342'	ITEM = 'B967','B677','O342'
2	ITEM = 'J833','UR09','ILY7'	ITEM = 'J833','UR09','ILY7'
3	ITEM = 'B967, OJ87'	ITEM = 'B967, OJ87'
4	(CATE = 'OBRT', 'TMPT') AND (ITEM = 'UR09','ILY7')	ITEM = 'UR09','ILY7')
5	CATE='ONTE,PNTE,QSDW,RVCG' OR ITEM=J833	ITEM=J833

Db-fiddle found here.

answered Aug 22, 2022 at 18:46

Isolated

6,4942 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NEWBIE Over a year ago

When I run SELECT ID, COND, SUBSTR(COND, INSTR(COND, 'ITEM', 1)) AS COND1 FROM A I am getting error message: Wrong number of arguments to function INSTR()

Isolated Over a year ago

What happens with this query: select INSTR('ABC TRIM 444', 'TRIM', 1) from dual;

Gary_W · Accepted Answer · 2022-08-25 15:13:52Z

Using REGEXP_SUBSTR() will allow you to capture ITEM and everything up to the last digit character OR a single quote (based on your sample data), thus eliminating that closing paren in ID 4. Be advised REGEXP_SUBSTR will add more overhead so based on the size of your data this may not be as efficient. Your mileage may vary.

WITH A(ID, cond) AS (
  SELECT 1, 'ITEM = ''B967'',''B677'',''O342''' FROM dual UNION ALL
  SELECT 2, 'ITEM = ''J833'',''UR09'',''ILY7''' FROM dual UNION ALL
  SELECT 3, 'ITEM = ''B967, OJ87'''             FROM dual UNION ALL
  SELECT 4, '(CATE = ''OBRT'', ''TMPT'') AND (ITEM = ''UR09'',''ILY7'')' FROM dual UNION ALL
  SELECT 5, 'CATE=''ONTE,PNTE,QSDW,RVCG'' OR ITEM=J833' FROM dual
)
SELECT ID, REGEXP_SUBSTR(cond, '(ITEM.*([[:digit:]]|''))') AS ITEM
from a;

   ID ITEM

     1 ITEM = 'B967','B677','O342'                       
     2 ITEM = 'J833','UR09','ILY7'                       
     3 ITEM = 'B967, OJ87'                               
     4 ITEM = 'UR09','ILY7'                              
     5 ITEM=J833

5 rows selected.

Collectives™ on Stack Overflow

SQL: Substring/Trim column values based on specific word

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related