Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Remove all Parenthetical Statements - including the parent

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Remove all Parenthetical Statements - including the parent
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: 2090
Location: UK

PostPosted: Fri Apr 27, 2018 1:27 pm    Post subject: Reply to: Remove all Parenthetical Statements - including the parent
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

Senior Member


Joined: 21 Sep 2010
Posts: 2016
Location: NY,USA

PostPosted: Sat Apr 28, 2018 2:19 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Remove selected duplicate rows from t... hnharish DFSORT/ICETOOL 3 Wed Sep 05, 2018 3:11 am
This topic is locked: you cannot edit posts or make replies. remove the gap b/w 2 fields shymala COBOL Programming 5 Thu Aug 02, 2018 12:05 pm
No new posts Need Help on sending email by includi... wik123 JCL & VSAM 9 Mon Jul 30, 2018 2:59 pm
This topic is locked: you cannot edit posts or make replies. I need to remove low values in my field shymala COBOL Programming 8 Sun Jul 29, 2018 11:15 am
No new posts How to remove spaces between variable... Selvarajars DFSORT/ICETOOL 7 Fri Jul 27, 2018 3:58 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us