SQL - adding space to a string

Question

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

Do these codes match a pattern, or are they looked up from somewhere? In your example 7/13 SICAVFT-BNMAPPL GERMAN, why do you pick BNM as the code and not VFT or PPL? — William Robertson
– William Robertson, Commented Dec 15, 2017 at 10:09
These codes don't follow pattern - they are distinct. VBN, BM etc are only a coding example to show that they differ from each other in letters and length. — marcin2x4
– marcin2x4, Commented Dec 18, 2017 at 13:10
Original codes I want to distinct with spaces are: FGIF, FTIF, FTOF, FTSF, FTSIF, FTSIIF, FTSMF , TISCE — marcin2x4
– marcin2x4, Commented Dec 18, 2017 at 13:17
Not VBN, BNM or XCV as in your question? The regex for the latest list is (FGIF|FTIF|FTOF|FTSF|FTSIF|FTSIIF|FTSMF|TISCE). — William Robertson
– William Robertson, Commented Dec 18, 2017 at 15:55
VBN, BNM were made up to show the lack of pattern in the naming. — marcin2x4
– marcin2x4, Commented Dec 18, 2017 at 15:57

William Robertson · Accepted Answer · 2017-12-19 11:30:12Z

1

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.)

edited Dec 19, 2017 at 11:30

answered Dec 13, 2017 at 15:47

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

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

4 Comments

marcin2x4 Over a year ago

The tricky part is that CODE actually looks different per row... TIG, TIGF, TIGI etc

marcin2x4 Over a year ago

I also tested it and it returns 'FGIF8 ' rather then 'FGIF 8'

William Robertson Over a year ago

So CODEn is not a string pattern but a value in some other column? I guess I missed that detail in your question ;)

marcin2x4 Over a year ago

Instead of CODE I get various stirng values like: FTIFXXX-AAA, FTIG XXX-AAA, FTTMS-XXX-AAA, FTTMS XXX-AAA etc ;)

Collectives™ on Stack Overflow

SQL - adding space to a string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related