compact2

set nocount on
set quoted_identifier off

SELECT
isnull(ltrim(rtrim(
case when ascii(substring(convert(varchar(50), INTERNIDENTITY),1,1)) = 194
then substring(convert(varchar(50), INTERNIDENTITY),2,49)
else convert(varchar(50), INTERNIDENTITY)
end
replace(replace(replace(
convert(varchar(50), INTERNIDENTITY), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), INTERNINSTID),1,1)) = 194
         then substring(convert(varchar(50), INTERNINSTID),2,49)
         else convert(varchar(50), INTERNINSTID)
    end
    replace(replace(replace(
        convert(varchar(50), INTERNINSTID), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), INTERNVERSION),1,1)) = 194
         then substring(convert(varchar(50), INTERNVERSION),2,49)
         else convert(varchar(50), INTERNVERSION)
    end
    replace(replace(replace(
        convert(varchar(50), INTERNVERSION), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), INTERNTIMESTAMP),1,1)) = 194
         then substring(convert(varchar(50), INTERNTIMESTAMP),2,49)
         else convert(varchar(50), INTERNTIMESTAMP)
    end
    replace(replace(replace(
        convert(varchar(50), INTERNTIMESTAMP), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), INTERNRECTYPE),1,1)) = 194
         then substring(convert(varchar(50), INTERNRECTYPE),2,49)
         else convert(varchar(50), INTERNRECTYPE)
    end
    replace(replace(replace(
        convert(varchar(50), INTERNRECTYPE), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), ORDERID),1,1)) = 194
         then substring(convert(varchar(50), ORDERID),2,49)
         else convert(varchar(50), ORDERID)
    end
    replace(replace(replace(
        convert(varchar(50), ORDERID), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), SYNCNUM),1,1)) = 194
         then substring(convert(varchar(50), SYNCNUM),2,49)
         else convert(varchar(50), SYNCNUM)
    end
    replace(replace(replace(
        convert(varchar(50), SYNCNUM), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), IDSOURCE),1,1)) = 194
         then substring(convert(varchar(50), IDSOURCE),2,49)
         else convert(varchar(50), IDSOURCE)
    end
    replace(replace(replace(
        convert(varchar(50), IDSOURCE), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), MEMOID),1,1)) = 194
         then substring(convert(varchar(50), MEMOID),2,49)
         else convert(varchar(50), MEMOID)
    end
    replace(replace(replace(
        convert(varchar(50), MEMOID), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), MEMOTYPE),1,1)) = 194
         then substring(convert(varchar(50), MEMOTYPE),2,49)
         else convert(varchar(50), MEMOTYPE)
    end
    replace(replace(replace(
        convert(varchar(50), MEMOTYPE), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(50), BIDPX),1,1)) = 194
         then substring(convert(varchar(50), BIDPX),2,49)
         else convert(varchar(50), BIDPX)
    end
    replace(replace(replace(
        convert(varchar(50), BIDPX), char(160), ' '), char(194), ''), char(173), '')
)), '') + '¬' +

-- Repeat same pattern for BIDSIZE, LASTSALE, MARKETCENTERID, MEMOCOMMENTS, OFFERPX, OFFERSIZE, ORDERIDS, ORDSYNCNUM

-- OVERRIDERULESET: remove CR/LF + leading 0xC2
isnull(ltrim(rtrim(
    case when ascii(substring(convert(varchar(16384), OVERRIDERULESET),1,1)) = 194
         then substring(convert(varchar(16384), OVERRIDERULESET),2,16383)
         else convert(varchar(16384), OVERRIDERULESET)
    end
    replace(replace(replace(replace(
        convert(varchar(16384), OVERRIDERULESET), char(13), ' '), char(10), ' '),
        char(160), ' '), char(173), '')
)), '') + '¬' +

-- Continue remaining columns: QUOTECONDITIONINDICATOR, QUOTETIME, RULE92COMMENT, SALESTRADERCOMMENTS, TICKDIRECTION, TOTALDOLVOL,
-- TOTALVOLUMETRADED, TRADERCOMMENTS, LULDLOWERPXBAND, LULDUPPERPXBAND, LULDPXBANDTIMESTAMP

FROM ZMemo
go