Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

need to remove quotes from output db2 query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon May 03, 2010 6:42 pm    Post subject: need to remove quotes from output db2 query
Reply with quote

I am retrieving the data from db2 table.
one of the field (length is 30) can have values like
a.) asfshf
b.) "jdfhsjf,jfhf"

I need to send the retrieved data to comma separated file.
for first type value , my output is having "asfshf"
for second type of value , my output will have ""jdhsjf,jfhf" ",

If i try to retrieve value from spufi it will return "jdhsjf,jfhf "

i want single quotes for second type of value. Please suggest if it is possible to get data without quotes while retrieving from db2 table itself.

Thanks in advance for help
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon May 03, 2010 6:58 pm    Post subject:
Reply with quote

post the query you are using... and sample columns values in the table...
Back to top
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon May 03, 2010 7:04 pm    Post subject: Reply to: need to remove quotes from output db2 query
Reply with quote

Code:
SELECT * FROM ISDBA.table1;

     ACN       REC_TYPE                   CUST_ NAM                 
------+---------+---------+---------+---------+---------+-
         14        07                            JDE PURPOSES ONLY
   27197         01                            "PRICKETTS DISTRIBUTING, INC."
Back to top
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon May 03, 2010 7:06 pm    Post subject: Reply to: need to remove quotes from output db2 query
Reply with quote

first field ACN is 9(09). second is char (02). cust_name is char 30
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon May 03, 2010 7:08 pm    Post subject:
Reply with quote

What would be expected output for about data?
would it be..
Code:

14 07 JDE PURPOSES ONLY
27197 01 PRICKETTS DISTRIBUTING, INC.
Back to top
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon May 03, 2010 7:17 pm    Post subject:
Reply with quote

yeah , thats what i am expecting.
Back to top
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon May 03, 2010 7:33 pm    Post subject:
Reply with quote

i can do it by
INSPECT OUT-RECord
REPLACING ALL '"' BY ' ' .

Please let me know if there any other way.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon May 03, 2010 7:57 pm    Post subject:
Reply with quote

What about the sql translate function.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1228
Location: Israel

PostPosted: Mon May 03, 2010 8:41 pm    Post subject: Reply to: need to remove quotes from output db2 query
Reply with quote

I just played a bit with a sample csv file and got interesting results:
Code:
1,AA,aa
2,AD,bb
3,AJ,"cc,ee"
4,AM,dd
5,BC,  gg
6,BF, "hh,kk"
7,BK, 'jj,mm'
8,BO, 'pp,rr'

Line 3: quoted text correctly loaded in one cell, double quotes have been removed.
Line 6: quoted text loaded in 2 cells, 1st part is "hh second part is kk"
Line 7: quoted text loaded in 2 cells, 1st part is 'jj 2nd part is mm'
Line 8: also loaded in 2 cells.
In lines 5 to 8, the leading space becomes part of the cell value.

I hope you can deduce by yourself what you need to do.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon May 03, 2010 8:43 pm    Post subject:
Reply with quote

Code:
select replace(cust_nam,'"', '') from
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 Translating Quotes Khadhar Basha CLIST & REXX 6 Tue May 09, 2017 11:52 am
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us