Joined: 05 Oct 2007
|Requirement - Remove all Parenthetical Statements - including the parenthesis
Column Value - 'United (States of) America'
Column data type - Char(100)
Need to replace '(States of)' with 1 space in sql.
Output should be - 'United America'
I have tried below query but no luck.
|select REPLACE(RTRIM(column1), SUBSTR(RTRIM(column1),
LOCATE('(', RTRIM(column1)), LENGTH(RTRIM(column1)) -
LOCATE(')', 'acirema (fo setats) detinu' ) -
LOCATE('(', RTRIM(column1)) + 2),'') as test
where column2 = 5
Could you please help me what went wrong or any other solution in DB2 SQL.
I have prepared above statement based on below query which I got from some web site.
|SELECT REPLACE(aud_desc, SUBSTR(aud_desc, LOCATE('[', aud_desc), LENGTH(aud_desc) -
LOCATE(']', REVERSE(aud_desc)) -
LOCATE('[', aud_desc) + 2), '') AS aud_desc .
As I am not able to use REVERSE in db2 sql, I have hard coded for now. I wrote SQL/PL routine to get reverse of column.
Appreicate your help.