REPLACE Statement

[benchpolo]

SQL Query
SELECT mh.hpcode
, ISNULL(CONVERT( varchar,mh.opfr omdt,112),'') as opfromdt
, ISNULL(CONVERT( varchar,mh.opth rudt,112),'') as opthrudt
, mc.patid
, mc.membid
, mc.lastnm
, mc.firstnm
, ISNULL(mc.mi,'' ) as 'mi'
, REPLACE(mc.stre et,char(10),'') as 'address'
, mc.city
, mc.state
, mc.zip
, mc.subssn as 'subscriberid'
, mc.ez_dbname as 'company'
, mh.currhist
, ISNULL(CONVERT( varchar,mc.birt h,112),'') as 'birth'
FROM memb_hphists mh
LEFT JOIN rvs_memb_compan y mc
ON (mh.memb_keyid= mc.memb_keyid) and (mc.EZ_dbname=m h.EZ_dbname)
WHERE mh.EZ_history IS NULL
AND mh.EZ_dbname <> 'DCRFK'
AND mc.membid IS NOT NULL
AND mh.currhist = 'C'

Issue
I have a sql above that extract data to a text file from one of our database. The problem im having is that several records have either a line feed or a carriage return. Is there a way to do a nested REPLACE query to eliminate either line feed or carriage return.

[iburyak]

Try this:

replace(replace(column_name, char(13), ''), char(10),'')

Good Luck.