Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum 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: 2247
Location: Hampshire, 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: 2299
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 User key common area users - How to r... vasanthz All Other Mainframe Topics 0 Thu Oct 10, 2019 1:17 am
No new posts IEBGENER to copy from FB to VB and re... Mike.Fulton All Other Mainframe Topics 3 Sat Aug 24, 2019 7:20 am
No new posts Summing records conditional statements. chillmo SYNCSORT 13 Wed May 15, 2019 2:12 am
No new posts To remove the spaces using SORT Card dharmaraok DFSORT/ICETOOL 1 Tue Apr 30, 2019 3:42 pm
No new posts Remove selected duplicate rows from t... hnharish DFSORT/ICETOOL 3 Wed Sep 05, 2018 3:11 am

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