CODEWITHSUNDEEP
sqldatabaseoracle-databasewhitespaceanalysis
marcin2x4
marcin2x4

Reputation: 1429

SQL - adding space to a string

I'm looking for a solution that would insert space after specific CODE sign appears in the entire string under DESCR attribute field.

Available data sample:

enter image description here

The overall logic is that SQL assigns specific CODE when it finds it with CASE else it assigns static, default CODE.

The CODEs are as follow: VBN, BNM, XCV. Random as I want to show that each single one has different length and letters pattern.

SELECT FUND_FAMILY,BUSINESS_UNIT_AP,VOUCHER_ID,INVOICE_ID, VENDOR_ID, DESCR
 FROM
 (
     SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) as RowNumb, FUND_FAMILY,BUSINESS_UNIT_AP,VOUCHER_ID,INVOICE_ID, VENDOR_ID, DESCR
     FROM (

SELECT FUND_FAMILY||BUSINESS_UNIT_AP||VOUCHER_ID||INVOICE_ID||VENDOR_ID ID,FUND_FAMILY,BUSINESS_UNIT_AP,VOUCHER_ID,INVOICE_ID, VENDOR_ID, 

DESCR FROM (
SELECT
VOUCHER_LINE_NUM,
     CASE instr (DESCR, 'XCV') 
     WHEN  (0) 

     THEN (CASE instr (DESCR, 'VBN') 
     WHEN  (0) 
     THEN  (CASE instr (DESCR, 'BNM') 
     WHEN  (0) 
     THEN  (CASE instr (DESCR, 'ASD') 
     WHEN  (0)
     THEN  (CASE instr (DESCR, 'ERT') 
     WHEN  (0) 
     THEN  (CASE instr (DESCR, 'ERY') 
     WHEN  (0)
     THEN  (CASE instr (DESCR, 'RTTYUU') 
     WHEN  (0)
     THEN  (CASE instr (DESCR, 'HJKKK') 
     WHEN  (0)

     THEN  'CODE2'  

     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE8'), 5) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE7'), 5) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE6'), 4) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE5'), 5) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE4'), 5) END)   
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE3'), 4) END)  
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE2'), 6) END)  

     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE1'), 4) 

     END AS FUND_FAMILY,

BUSINESS_UNIT_AP,VOUCHER_ID,INVOICE_ID,VENDOR_ID, DESCR FROM TABLE_NAME 

WHERE UNIT IN ('ETITY1')
AND ACCOUNT = '135000' 
AND FISCAL_YEAR > 2013

AND (DESCR LIKE '%CODE1%' OR DESCR LIKE '%CODE2%' OR DESCR LIKE '%CODE3%' OR DESCR LIKE '%CODE4%' OR DESCR LIKE '%CODE5%'
OR DESCR LIKE '%CODE6%' OR  DESCR LIKE '%CODE7%' OR DESCR LIKE '%CODE8%' OR DESCR LIKE '%CODE9%')

AND NOT DESCR LIKE '%CODE10%'

GROUP BY 
VOUCHER_LINE_NUM , 
     CASE instr (DESCR, 'CODE1') 
     WHEN  (0) 

     THEN (CASE instr (DESCR, 'CODE2') 
     WHEN  (0) 
     THEN  (CASE instr (DESCR, 'CODE3') 
     WHEN  (0) 
     THEN  (CASE instr (DESCR, 'CODE4') 
     WHEN  (0)
     THEN  (CASE instr (DESCR, 'CODE5') 
     WHEN  (0) 
     THEN  (CASE instr (DESCR, 'CODE6') 
     WHEN  (0)
     THEN  (CASE instr (DESCR, 'CODE7') 
     WHEN  (0)
     THEN  (CASE instr (DESCR, 'CODE8') 
     WHEN  (0)

     THEN  'CODE2'  

     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE8'), 5) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE7'), 5) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE6'), 4) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE5'), 5) END)
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE4'), 5) END)   
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE3'), 4) END)  
     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE2'), 6) END)

     ELSE  SUBSTR(DESCR, instr (DESCR, 'CODE1'), 4) 
     END, BUSINESS_UNIT_AP,VOUCHER_ID,INVOICE_ID,VENDOR_ID, DESCR


    )
        )
            ) X 
WHERE RowNumb = 1

Upvotes: 0

Views: 1652

Answers (1)

William Robertson
William Robertson

Reputation: 15991

Sounds like a job for regexp_replace.

trim(regexp_replace(descr,'( )*(FGIF|FTIF|FTOF|FTSF|FTSIF|FTSIIF|FTSMF|TISCE)( )*',' \2 '))

The regex looks for [zero or more spaces][any one of your codes][zero or more spaces] and replaces it with [ ][the code it found][ ]. The trim() is needed in case the code appears at the start or end of a string. (I expect there is a way to handle this within the regex itself, but just triming the whole expression is a heck of a lot easier.)

Upvotes: 1

Related Questions