SQL: Substring/Trim column values based on specific word-CodePudding

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                  |

CodePudding user response：

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.

CodePudding user response：

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.