Shaunn,
Sorry for the one day delay reaction but yesterday was a holyday.
You can use the REPLACE function,but since this function can only replace
one string at the time, you've to repeat the function if you want to
replace more than one special character.
Like this: SELECT REPLACE( REPLACE( REPLACE('[EMAIL PROTECTED]','\','') ,'
@','-') ,'^','') FROM SYSIBM.SYSDUMMY1;
Which results into: H.RUS=OTRA.NL
With kind regards,
Hessel
Met vriendelijke groeten,
Hessel
"Johnson,
Shaunn" To: "'[EMAIL PROTECTED]'" <[EMAIL
PROTECTED]>
<[EMAIL PROTECTED] cc:
.com> Subject: [DB2EUG] RE: sql to return
space or null field - UPDATE
Sent by:
[EMAIL PROTECTED]
wash.org
09-06-2003 18:45
Please respond
to "Johnson,
Shaunn"
--Howdy:
--I figured out what was happening with the data
--I'm pulling from DB2. There are some funky characters
--in the table that I need to get rid of. Thinks like back slashes
--and ' ^ ' and like.
--While I've heard that it is perfectly legal to have these
--characters in the table, that does not make it
--data that I can use.
--So, I need to readdress this question: Instead of
--returning a null or space, is there a way to search
--and remove data fields that have characters like
-- " \ " in them?
--Thanks!
-X
-----Original Message-----
Sent: Friday, June 06, 2003 11:12 AM
To: [EMAIL PROTECTED]
Subject: sql to return space or null field
Howdy:
I'm working with DB2 OS/390 6.1.2.
I'm trying to write a query where return a few
rows and append that data into a text file.
[snip some code]
select
c_auth_num,
c_auth_reason,
c_bill_prov,
c_claim_num,
c_claim_type,
c_contract_num,
c_denied_reason,
c_diag_1
from hdxx0000.htxx0_clxxdemo
where d_from_dt between '01/01/2001' and '06/30/2001'
and
c_stat_cd like 'Y%' and
c_stat_reason not in('AV','VC','VS','Y15') and
c_diag_1 not like 'INT%' and
c_proc_cd not like 'INT%' and
c_type_serv <> 'IN'
[/snip some code]
This seems to work find ... sometimes. What I am looking
at is that somehow, some where, when I want to manipulate
the data, send it to another database or whatever, I get
errors like these
[snip error]
copy: line 1284849, value too long for type character(12)
[/snip error]
And when I try to look at the text file and figure out what it's
complaining
about (matching the field against what the table structure is)
and it *looks* like it could fit, but there could also be something
in the field that may / may not belong. It is my understanding that
DB2 accepts almost all ANSI characters so, even if it was a
^M or whatever, it would *still* fit in the table (as far as DB2
is concerned).
My question: Is there a way to explicitly say
select column (set length to 12 characters)
from table? OR, maybe better if there
is a way to say:
if the field returns a blank, force it to be null ?
Thanks in advance!
-X
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
