CODEWITHSUNDEEP
sql-serversql-server-2008
shannu
shannu

Reputation: 187

how to get string data from the column of the data table in sql server

hi i am trying to convert &#x0A into &#x0D&#x0A in select query 

SELECT TOP 1  
 @F0 = ID,  
 @F1 = MessageSource,  
 @F2 = MessageID,  
 @F3 = MessageText  
FROM [dbo].[sample_MESSAGE]  
WHERE MessageStatus = 'FR' and MessageSource=@Param1   -- VALID  --Lavanya

SELECT @F0 AS F0, @F1 AS F1, @F2 AS F2, replace(@F3,'
','
') AS F3  
FOR XML RAW

but it was not replacing the values which i was assigned, can any one suggest me on this

Upvotes: 0

Views: 954

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21766

It works:

DECLARE @F0 INT, @F2 INT, @F1 INT, @F3 VARCHAR(MAX)
SELECT 
 @F0 = 1,  
 @F1 = 2,  
 @F2 = 3,  
 @F3 = '
'  


SELECT @F0 AS F0, @F1 AS F1, @F2 AS F2, replace(@F3,'
','
') AS F3  
FOR XML RAW

so

you have to find out what is bad in your text data. If text contains chars with codes 10 - you should not to make REPLACE to find string like this '
' replace CHAR(10) to CHAR(10)_CHAR(13) instead

Upvotes: 0

Andriy M
Andriy M

Reputation: 77677

Maybe you mean this:

  … REPLACE(@F3, CHAR(10), CHAR(13) + CHAR(10)) AS F3 …

or this:

  … REPLACE(@F3, NCHAR(10), NCHAR(13) + NCHAR(10)) AS F3 …

depending on whether @F3 is varchar or nvarchar.

Upvotes: 1

Related Questions