odd character when using char(13) and char(10)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

odd character when using char(13) and char(10)

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-02 : 15:40:46

In the trigger below.
i am trying to put the values in with a carriage return and line feed.
the carriage return seems to be working fine but after each value I get the following '#x0D;' I believe this may be the character entity for a carriage return.

can anyone tell me how to get rid of it?

here's an example of the finished memo field

Affected ahrb-3#x0D;
Affected cablecom-exeter-il-1#x0D;
Affected city-bristol-coll-4#x0D;
Affected N.av#x0D;
Affected north-devon-coll-6#x0D;
Affected st-brendans-sfc-2#x0D;
Affected workers-edu-bris-1#x0D;

The trigger is below

USE [Almouth]
GO
/****** Object: Trigger [dbo].[SITE_UPDATE] Script Date: 09/02/2010 20:26:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[SITE_UPDATE]
ON [dbo].[Detail]
FOR INSERT,
UPDATE
AS

SET NOCOUNT ON

DECLARE @Stage TABLE
(
CallID varchar (8),
[Name] VARCHAR(50),
[Rating] varchar(1)
)

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, Clifton01N, Clifton01R FROM inserted WHERE Clifton01Y = 'X' AND Clifton01N > '' UNION ALL
SELECT CallID, Clifton02N, Clifton02R FROM inserted WHERE Clifton02Y = 'X' AND Clifton02N > '' UNION ALL
SELECT CallID, Clifton03N, Clifton03R FROM inserted WHERE Clifton03Y = 'X' AND Clifton03N > '' UNION ALL
SELECT CallID, Clifton04N, Clifton04R FROM inserted WHERE Clifton04Y = 'X' AND Clifton04N > '' UNION ALL
SELECT CallID, Clifton05N, Clifton05R FROM inserted WHERE Clifton05Y = 'X' AND Clifton05N > '' UNION ALL
SELECT CallID, Clifton06N, Clifton06R FROM inserted WHERE Clifton06Y = 'X' AND Clifton06N > '' UNION ALL
SELECT CallID, Clifton07N, Clifton07R FROM inserted WHERE Clifton07Y = 'X' AND Clifton07N > '' UNION ALL
SELECT CallID, Clifton08N, Clifton08R FROM inserted WHERE Clifton08Y = 'X' AND Clifton08N > '' UNION ALL
SELECT CallID, Clifton09N, Clifton09R FROM inserted WHERE Clifton09Y = 'X' AND Clifton09N > '' UNION ALL
SELECT CallID, Clifton10N, Clifton10R FROM inserted WHERE Clifton10Y = 'X' AND Clifton10N > '' UNION ALL
SELECT CallID, Clifton11N, Clifton11R FROM inserted WHERE Clifton11Y = 'X' AND Clifton11N > '' UNION ALL
SELECT CallID, Clifton12N, Clifton12R FROM inserted WHERE Clifton12Y = 'X' AND Clifton12N > '' UNION ALL
SELECT CallID, Clifton13N, Clifton13R FROM inserted WHERE Clifton13Y = 'X' AND Clifton13N > '' UNION ALL
SELECT CallID, Clifton14N, Clifton14R FROM inserted WHERE Clifton14Y = 'X' AND Clifton14N > '' UNION ALL
SELECT CallID, Clifton15N, Clifton15R FROM inserted WHERE Clifton15Y = 'X' AND Clifton15N > '' UNION ALL
SELECT CallID, Clifton16N, Clifton16R FROM inserted WHERE Clifton16Y = 'X' AND Clifton16N > '' UNION ALL
SELECT CallID, Clifton17N, Clifton17R FROM inserted WHERE Clifton17Y = 'X' AND Clifton17N > '' UNION ALL
SELECT CallID, Clifton18N, Clifton18R FROM inserted WHERE Clifton18Y = 'X' AND Clifton18N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, Frenchay01N, Frenchay01R FROM inserted WHERE Frenchay01Y = 'X' AND Frenchay01N > '' UNION ALL
SELECT CallID, Frenchay02N, Frenchay02R FROM inserted WHERE Frenchay02Y = 'X' AND Frenchay02N > '' UNION ALL
SELECT CallID, Frenchay03N, Frenchay03R FROM inserted WHERE Frenchay03Y = 'X' AND Frenchay03N > '' UNION ALL
SELECT CallID, Frenchay04N, Frenchay04R FROM inserted WHERE Frenchay04Y = 'X' AND Frenchay04N > '' UNION ALL
SELECT CallID, Frenchay05N, Frenchay05R FROM inserted WHERE Frenchay05Y = 'X' AND Frenchay05N > '' UNION ALL
SELECT CallID, Frenchay06N, Frenchay06R FROM inserted WHERE Frenchay06Y = 'X' AND Frenchay06N > '' UNION ALL
SELECT CallID, Frenchay07N, Frenchay07R FROM inserted WHERE Frenchay07Y = 'X' AND Frenchay07N > '' UNION ALL
SELECT CallID, Frenchay08N, Frenchay08R FROM inserted WHERE Frenchay08Y = 'X' AND Frenchay08N > '' UNION ALL
SELECT CallID, Frenchay09N, Frenchay09R FROM inserted WHERE Frenchay09Y = 'X' AND Frenchay09N > '' UNION ALL
SELECT CallID, Frenchay10N, Frenchay10R FROM inserted WHERE Frenchay10Y = 'X' AND Frenchay10N > '' UNION ALL
SELECT CallID, Frenchay11N, Frenchay11R FROM inserted WHERE Frenchay11Y = 'X' AND Frenchay11N > '' UNION ALL
SELECT CallID, Frenchay12N, Frenchay12R FROM inserted WHERE Frenchay12Y = 'X' AND Frenchay12N > '' UNION ALL
SELECT CallID, Frenchay13N, Frenchay13R FROM inserted WHERE Frenchay13Y = 'X' AND Frenchay13N > '' UNION ALL
SELECT CallID, Frenchay14N, Frenchay14R FROM inserted WHERE Frenchay14Y = 'X' AND Frenchay14N > '' UNION ALL
SELECT CallID, Frenchay15N, Frenchay15R FROM inserted WHERE Frenchay15Y = 'X' AND Frenchay15N > '' UNION ALL
SELECT CallID, Frenchay16N, Frenchay16R FROM inserted WHERE Frenchay16Y = 'X' AND Frenchay16N > '' UNION ALL
SELECT CallID, Frenchay17N, Frenchay17R FROM inserted WHERE Frenchay17Y = 'X' AND Frenchay17N > '' UNION ALL
SELECT CallID, Frenchay18N, Frenchay18R FROM inserted WHERE Frenchay18Y = 'X' AND Frenchay18N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, ExeUni01N, ExeUni01R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni01N > '' UNION ALL
SELECT CallID, ExeUni02N, ExeUni02R FROM inserted WHERE ExeUni02Y = 'X' AND ExeUni02N > '' UNION ALL
SELECT CallID, ExeUni03N, ExeUni03R FROM inserted WHERE ExeUni03Y = 'X' AND ExeUni03N > '' UNION ALL
SELECT CallID, ExeUni04N, ExeUni04R FROM inserted WHERE ExeUni04Y = 'X' AND ExeUni04N > '' UNION ALL
SELECT CallID, ExeUni05N, ExeUni05R FROM inserted WHERE ExeUni05Y = 'X' AND ExeUni05N > '' UNION ALL
SELECT CallID, ExeUni06N, ExeUni06R FROM inserted WHERE ExeUni06Y = 'X' AND ExeUni06N > '' UNION ALL
SELECT CallID, ExeUni07N, ExeUni07R FROM inserted WHERE ExeUni07Y = 'X' AND ExeUni07N > '' UNION ALL
SELECT CallID, ExeUni08N, ExeUni08R FROM inserted WHERE ExeUni08Y = 'X' AND ExeUni08N > '' UNION ALL
SELECT CallID, ExeUni09N, ExeUni09R FROM inserted WHERE ExeUni09Y = 'X' AND ExeUni09N > '' UNION ALL
SELECT CallID, ExeUni10N, ExeUni10R FROM inserted WHERE ExeUni10Y = 'X' AND ExeUni10N > '' UNION ALL
SELECT CallID, ExeUni11N, ExeUni11R FROM inserted WHERE ExeUni11Y = 'X' AND ExeUni11N > '' UNION ALL
SELECT CallID, ExeUni12N, ExeUni12R FROM inserted WHERE ExeUni12Y = 'X' AND ExeUni12N > '' UNION ALL
SELECT CallID, ExeUni13N, ExeUni13R FROM inserted WHERE ExeUni13Y = 'X' AND ExeUni13N > '' UNION ALL
SELECT CallID, ExeUni14N, ExeUni14R FROM inserted WHERE ExeUni14Y = 'X' AND ExeUni14N > '' UNION ALL
SELECT CallID, ExeUni15N, ExeUni15R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni15N > '' UNION ALL
SELECT CallID, ExeUni16N, ExeUni16R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni16N > '' UNION ALL
SELECT CallID, ExeUni17N, ExeUni17R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni17N > '' UNION ALL
SELECT CallID, ExeUni18N, ExeUni18R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni18N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, BathCD01N, BathCD01R FROM inserted WHERE BathCD01Y = 'X' AND BathCD01N > '' UNION ALL
SELECT CallID, BathCD02N, BathCD02R FROM inserted WHERE BathCD02Y = 'X' AND BathCD02N > '' UNION ALL
SELECT CallID, BathCD03N, BathCD03R FROM inserted WHERE BathCD03Y = 'X' AND BathCD03N > '' UNION ALL
SELECT CallID, BathCD04N, BathCD04R FROM inserted WHERE BathCD04Y = 'X' AND BathCD04N > '' UNION ALL
SELECT CallID, BathCD05N, BathCD05R FROM inserted WHERE BathCD05Y = 'X' AND BathCD05N > '' UNION ALL
SELECT CallID, BathCD06N, BathCD06R FROM inserted WHERE BathCD06Y = 'X' AND BathCD06N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, BathJW01N, BathJW01R FROM inserted WHERE BathJW01Y = 'X' AND BathJW01N > '' UNION ALL
SELECT CallID, BathJW02N, BathJW02R FROM inserted WHERE BathJW02Y = 'X' AND BathJW02N > '' UNION ALL
SELECT CallID, BathJW03N, BathJW03R FROM inserted WHERE BathJW03Y = 'X' AND BathJW03N > '' UNION ALL
SELECT CallID, BathJW04N, BathJW04R FROM inserted WHERE BathJW04Y = 'X' AND BathJW04N > '' UNION ALL
SELECT CallID, BathJW05N, BathJW05R FROM inserted WHERE BathJW05Y = 'X' AND BathJW05N > '' UNION ALL
SELECT CallID, BathJW06N, BathJW06R FROM inserted WHERE BathJW06Y = 'X' AND BathJW06N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, CheltUoG01X, CheltUoG01R FROM inserted WHERE CheltUoG01 = 'X' AND CheltUoG01X > '' UNION ALL
SELECT CallID, CheltUoG02X, CheltUoG02R FROM inserted WHERE CheltUoG02 = 'X' AND CheltUoG02X > '' UNION ALL
SELECT CallID, CheltUoG03X, CheltUoG03R FROM inserted WHERE CheltUoG03 = 'X' AND CheltUoG03X > '' UNION ALL
SELECT CallID, CheltUoG04X, CheltUoG04R FROM inserted WHERE CheltUoG04 = 'X' AND CheltUoG04X > '' UNION ALL
SELECT CallID, CheltUoG05X, CheltUoG05R FROM inserted WHERE CheltUoG05 = 'X' AND CheltUoG05X > '' UNION ALL
SELECT CallID, CheltUoG06X, CheltUoG06R FROM inserted WHERE CheltUoG06 = 'X' AND CheltUoG06X > '' UNION ALL
SELECT CallID, CheltUoG07X, CheltUoG07R FROM inserted WHERE CheltUoG07 = 'X' AND CheltUoG07X > '' UNION ALL
SELECT CallID, CheltUoG08X, CheltUoG08R FROM inserted WHERE CheltUoG08 = 'X' AND CheltUoG08X > '' UNION ALL
SELECT CallID, CheltUoG09X, CheltUoG09R FROM inserted WHERE CheltUoG09 = 'X' AND CheltUoG09X > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, SCAT01N, SCAT01R FROM inserted WHERE SCAT01Y = 'X' AND SCAT01N > '' UNION ALL
SELECT CallID, SCAT02N, SCAT02R FROM inserted WHERE SCAT02Y = 'X' AND SCAT02N > '' UNION ALL
SELECT CallID, SCAT03N, SCAT03R FROM inserted WHERE SCAT03Y = 'X' AND SCAT03N > '' UNION ALL
SELECT CallID, SCAT04N, SCAT04R FROM inserted WHERE SCAT04Y = 'X' AND SCAT04N > '' UNION ALL
SELECT CallID, SCAT05N, SCAT05R FROM inserted WHERE SCAT05Y = 'X' AND SCAT05N > '' UNION ALL
SELECT CallID, SCAT06N, SCAT06R FROM inserted WHERE SCAT06Y = 'X' AND SCAT06N > '' UNION ALL
SELECT CallID, SCAT07N, SCAT07R FROM inserted WHERE SCAT07Y = 'X' AND SCAT07N > '' UNION ALL
SELECT CallID, SCAT08N, SCAT08R FROM inserted WHERE SCAT08Y = 'X' AND SCAT08N > '' UNION ALL
SELECT CallID, SCAT09N, SCAT09R FROM inserted WHERE SCAT09Y = 'X' AND SCAT09N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, CheltglosCol01N, CheltglosCol01R FROM inserted WHERE CheltglosCol01Y = 'X' AND CheltglosCol01N > '' UNION ALL
SELECT CallID, CheltglosCol02N, CheltglosCol02R FROM inserted WHERE CheltglosCol02Y = 'X' AND CheltglosCol02N > '' UNION ALL
SELECT CallID, CheltglosCol03N, CheltglosCol03R FROM inserted WHERE CheltglosCol03Y = 'X' AND CheltglosCol03N > '' UNION ALL
SELECT CallID, CheltglosCol04N, CheltglosCol04R FROM inserted WHERE CheltglosCol04Y = 'X' AND CheltglosCol04N > '' UNION ALL
SELECT CallID, CheltglosCol05N, CheltglosCol05R FROM inserted WHERE CheltglosCol05Y = 'X' AND CheltglosCol05N > '' UNION ALL
SELECT CallID, CheltglosCol06N, CheltglosCol06R FROM inserted WHERE CheltglosCol06Y = 'X' AND CheltglosCol06N > '' UNION ALL
SELECT CallID, CheltglosCol07N, CheltglosCol07R FROM inserted WHERE CheltglosCol07Y = 'X' AND CheltglosCol07N > '' UNION ALL
SELECT CallID, CheltglosCol08N, CheltglosCol08R FROM inserted WHERE CheltglosCol08Y = 'X' AND CheltglosCol08N > '' UNION ALL
SELECT CallID, CheltglosCol09N, CheltglosCol09R FROM inserted WHERE CheltglosCol09Y = 'X' AND CheltglosCol09N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, SponsCon01N, SponsCon01R FROM inserted WHERE SponsCon01Y = 'X' AND SponsCon01N > '' UNION ALL
SELECT CallID, SponsCon02N, SponsCon02R FROM inserted WHERE SponsCon02Y = 'X' AND SponsCon02N > '' UNION ALL
SELECT CallID, SponsCon03N, SponsCon03R FROM inserted WHERE SponsCon03Y = 'X' AND SponsCon03N > '' UNION ALL
SELECT CallID, SponsCon04N, SponsCon04R FROM inserted WHERE SponsCon04Y = 'X' AND SponsCon04N > '' UNION ALL
SELECT CallID, SponsCon05N, SponsCon05R FROM inserted WHERE SponsCon05Y = 'X' AND SponsCon05N > '' UNION ALL
SELECT CallID, SponsCon06N, SponsCon06R FROM inserted WHERE SponsCon06Y = 'X' AND SponsCon06N > '' UNION ALL
SELECT CallID, SponsCon07N, SponsCon07R FROM inserted WHERE SponsCon07Y = 'X' AND SponsCon07N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, ClinAcad01N, ClinAcad01R FROM inserted WHERE ClinAcad01Y = 'X' AND ClinAcad01N > '' UNION ALL
SELECT CallID, ClinAcad02N, ClinAcad02R FROM inserted WHERE ClinAcad02Y = 'X' AND ClinAcad02N > '' UNION ALL
SELECT CallID, ClinAcad03N, ClinAcad03R FROM inserted WHERE ClinAcad03Y = 'X' AND ClinAcad03N > '' UNION ALL
SELECT CallID, ClinAcad04N, ClinAcad04R FROM inserted WHERE ClinAcad04Y = 'X' AND ClinAcad04N > '' UNION ALL
SELECT CallID, ClinAcad05N, ClinAcad05R FROM inserted WHERE ClinAcad05Y = 'X' AND ClinAcad05N > '' UNION ALL
SELECT CallID, ClinAcad06N, ClinAcad06R FROM inserted WHERE ClinAcad06Y = 'X' AND ClinAcad06N > '' UNION ALL
SELECT CallID, ClinAcad07N, ClinAcad07R FROM inserted WHERE ClinAcad07Y = 'X' AND ClinAcad07N > '' UNION ALL
SELECT CallID, ClinAcad08N, ClinAcad08R FROM inserted WHERE ClinAcad08Y = 'X' AND ClinAcad08N > '' UNION ALL
SELECT CallID, ClinAcad09N, ClinAcad09R FROM inserted WHERE ClinAcad09Y = 'X' AND ClinAcad09N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, ExeCol01N, ExeCol01R FROM inserted WHERE ExeCol01Y = 'X' AND ExeCol01N > '' UNION ALL
SELECT CallID, ExeCol02N, ExeCol02R FROM inserted WHERE ExeCol02Y = 'X' AND ExeCol02N > '' UNION ALL
SELECT CallID, ExeCol03N, ExeCol03R FROM inserted WHERE ExeCol03Y = 'X' AND ExeCol03N > '' UNION ALL
SELECT CallID, ExeCol04N, ExeCol04R FROM inserted WHERE ExeCol04Y = 'X' AND ExeCol04N > '' UNION ALL
SELECT CallID, ExeCol05N, ExeCol05R FROM inserted WHERE ExeCol05Y = 'X' AND ExeCol05N > '' UNION ALL
SELECT CallID, ExeCol06N, ExeCol06R FROM inserted WHERE ExeCol06Y = 'X' AND ExeCol06N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, PlymCity01N, PlymCity01R FROM inserted WHERE PlymCity01Y = 'X' AND PlymCity01N > '' UNION ALL
SELECT CallID, PlymCity02N, PlymCity02R FROM inserted WHERE PlymCity02Y = 'X' AND PlymCity02N > '' UNION ALL
SELECT CallID, PlymCity03N, PlymCity03R FROM inserted WHERE PlymCity03Y = 'X' AND PlymCity03N > '' UNION ALL
SELECT CallID, PlymCity04N, PlymCity04R FROM inserted WHERE PlymCity04Y = 'X' AND PlymCity04N > '' UNION ALL
SELECT CallID, PlymCity05N, PlymCity05R FROM inserted WHERE PlymCity05Y = 'X' AND PlymCity05N > '' UNION ALL
SELECT CallID, PlymCity06N, PlymCity06R FROM inserted WHERE PlymCity06Y = 'X' AND PlymCity06N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, PlymUni01N, PlymUni01R FROM inserted WHERE PlymUni01Y = 'X' AND PlymUni01N > '' UNION ALL
SELECT CallID, PlymUni02N, PlymUni02R FROM inserted WHERE PlymUni02Y = 'X' AND PlymUni02N > '' UNION ALL
SELECT CallID, PlymUni03N, PlymUni03R FROM inserted WHERE PlymUni03Y = 'X' AND PlymUni03N > '' UNION ALL
SELECT CallID, PlymUni04N, PlymUni04R FROM inserted WHERE PlymUni04Y = 'X' AND PlymUni04N > '' UNION ALL
SELECT CallID, PlymUni05N, PlymUni05R FROM inserted WHERE PlymUni05Y = 'X' AND PlymUni05N > '' UNION ALL
SELECT CallID, PlymUni06N, PlymUni06R FROM inserted WHERE PlymUni06Y = 'X' AND PlymUni06N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, BridCol01N, BridCol01R FROM inserted WHERE BridCol01Y = 'X' AND BridCol01N > '' UNION ALL
SELECT CallID, BridCol02N, BridCol02R FROM inserted WHERE BridCol02Y = 'X' AND BridCol02N > '' UNION ALL
SELECT CallID, BridCol03N, BridCol03R FROM inserted WHERE BridCol03Y = 'X' AND BridCol03N > '' UNION ALL
SELECT CallID, BridCol04N, BridCol04R FROM inserted WHERE BridCol04Y = 'X' AND BridCol04N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, RAC01N, RAC01R FROM inserted WHERE RAC01Y = 'X' AND RAC01N > '' UNION ALL
SELECT CallID, RAC02N, RAC02R FROM inserted WHERE RAC02Y = 'X' AND RAC02N > '' UNION ALL
SELECT CallID, RAC03N, RAC03R FROM inserted WHERE RAC03Y = 'X' AND RAC03N > '' UNION ALL
SELECT CallID, RAC04N, RAC04R FROM inserted WHERE RAC04Y = 'X' AND RAC04N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, CIRENCESTER01N, CIRENCESTER01R FROM inserted WHERE CIRENCESTER01Y = 'X' AND CIRENCESTER01N > '' UNION ALL
SELECT CallID, CIRENCESTER02N, CIRENCESTER02R FROM inserted WHERE CIRENCESTER02Y = 'X' AND CIRENCESTER02N > '' UNION ALL
SELECT CallID, CIRENCESTER03N, CIRENCESTER03R FROM inserted WHERE CIRENCESTER03Y = 'X' AND CIRENCESTER03N > '' UNION ALL
SELECT CallID, CIRENCESTER04N, CIRENCESTER04R FROM inserted WHERE CIRENCESTER04Y = 'X' AND CIRENCESTER04N > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, POP101X, POP101R FROM inserted WHERE POP101Y = 'X' AND POP101X > '' UNION ALL
SELECT CallID, POP102X, POP102R FROM inserted WHERE POP102Y = 'X' AND POP102X > ''

INSERT @Stage (CallID, [Name], [Rating])
SELECT CallID, POP201N, POP201R FROM inserted WHERE POP201Y = 'X' AND POP201N > '' UNION ALL
SELECT CallID, POP202N, POP202R FROM inserted WHERE POP202Y = 'X' AND POP202N > ''

DECLARE @Formatting TABLE
(
CallID varchar (8),
CircuitName VARCHAR(20)
)

INSERT @Formatting (CallID, CircuitName)
SELECT DISTINCT s.CallID,c.CircuitName
FROM @Stage AS s
INNER JOIN dbo.Circuit AS c ON c.CircuitDesc = s.[Name]
AND c.CircuitRole = s.[Rating]

UPDATE cl
SET cl.swernSite = RTRIM(f.sitenames)
FROM dbo.CallLog AS cl
CROSS APPLY (
SELECT 'Affected' + Char(9) + x.CircuitName + Char(13) + Char(10)
FROM @Formatting AS x
WHERE x.CallID = cl.CallID
FOR XML PATH('')
) AS f(SiteNames)
WHERE EXISTS (SELECT * FROM @Formatting AS y WHERE y.CallID = cl.CallID)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-02 : 15:54:00

That's a side-effect of the FOR XML PATH conversion, it's encoding the CHAR(13) character. Better to use a different character to concatenate, like "*", or something you know isn't in the data, and then replace that with char(13) + char(10):

UPDATE	 cl
SET cl.swernSite = replace(RTRIM(f.sitenames),'*', char(13)+char(10))
FROM	 dbo.CallLog AS cl
CROSS APPLY	(
SELECT 'Affected' + Char(9) + x.CircuitName + '*'
FROM	@Formatting AS x
WHERE	x.CallID = cl.CallID
FOR XML	PATH('')
) AS f(SiteNames)
WHERE	 EXISTS (SELECT * FROM @Formatting AS y WHERE y.CallID = cl.CallID)

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-04 : 02:58:35

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources