IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Remove all Parenthetical Statements - including the parent


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
JayaprakashT

New User


Joined: 05 Oct 2007
Posts: 3
Location: Hyderabad

PostPosted: Fri Apr 27, 2018 3:57 am
Reply with quote

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.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2448
Location: Hampshire, UK

PostPosted: Fri Apr 27, 2018 1:27 pm
Reply with quote

More than 10 years using a forum and you still cannot use code tags to present your code and data? Coded for you this time.

How did it not work? Show the result. I am not going to waste my time setting up data to run your query to find out what you already know.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Sat Apr 28, 2018 2:19 am
Reply with quote

Is this a single occurrence or multiples for a a single row?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Revoke on DB2 to remove an user DB2 4
No new posts REMOVE DUPLICATE RECORDS BASED ON A S... DFSORT/ICETOOL 4
No new posts How to remove this shortcut bar? TSO/ISPF 3
No new posts How to remove remote file older than ... All Other Mainframe Topics 4
No new posts filter COMMIT/ROLLBACK statements DFSORT/ICETOOL 13
Search our Forums:

Back to Top