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: 2031
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: 1938
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 duplicate record with condition rajiv rengasamy DFSORT/ICETOOL 4 Wed Jul 11, 2018 3:55 pm
No new posts To remove spaces from a VB file using... Selvarajars DFSORT/ICETOOL 5 Thu Jun 21, 2018 6:57 pm
No new posts PLI %IF statements Pedro PL/I & Assembler 1 Tue Sep 19, 2017 12:06 am
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am

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