JayaprakashT
New User
Joined: 05 Oct 2007
Posts: 3
Location: Hyderabad
|
|
|
|
Requirement - Remove all Parenthetical Statements - including the parenthesis
Ex:
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.
| Code: |
select REPLACE(RTRIM(column1), SUBSTR(RTRIM(column1),
LOCATE('(', RTRIM(column1)), LENGTH(RTRIM(column1)) -
LOCATE(')', 'acirema (fo setats) detinu' ) -
LOCATE('(', RTRIM(column1)) + 2),'') as test
from table1
where column2 = 5
with ur; |
Could you please help me what went wrong or any other solution in DB2 SQL.
Note:
I have prepared above statement based on below query which I got from some web site.
| Code: |
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. |
|
