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

Question

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,'&#x0A;','&#x0D;&#x0A;') AS F3  
FOR XML RAW

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

You're not closing the parens on REPLACE. That should be issuing a syntax error. — bfavaretto
– bfavaretto, Commented Feb 9, 2012 at 14:11
sry for the trouble, i have edited see my question, check it now,i hope it was not targeting the data of the messagetext column — shannu
– shannu, Commented Feb 9, 2012 at 14:15
Do you want to search and replace the string &#x0A or the value ? Because if your looking to replace the value, right now your searching the string... — FMaz008
– FMaz008, Commented Feb 9, 2012 at 14:16
Are you expecting it to replace the strings as shown, or to perform some conversion of hexadecimal values to control characters, e.g. carriage return and line feed? — HABO
– HABO, Commented Feb 9, 2012 at 14:17
Maybe its because you try to replace the value &#x0A, but you are searching 
, you know, with an extra semicolon — Lamak
– Lamak, Commented Feb 9, 2012 at 14:18

Andriy M · Accepted Answer · 2012-02-09 14:16:09Z

1

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.

answered Feb 9, 2012 at 14:16

Andriy M

78.1k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleg Dok · Accepted Answer · 2012-02-09 14:19:16Z

0

It works:

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


SELECT @F0 AS F0, @F1 AS F1, @F2 AS F2, replace(@F3,'&#x0A;','&#x0D;&#x0A;') 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

answered Feb 9, 2012 at 14:19

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related