CODEWITHSUNDEEP
sql-server-2008
rahul
rahul

Reputation: 7663

< converted to < in sql server

i have some data in my table

when i select that data with this query

select TblActionHistories.Comments from TblActionHistories WHERE TblActionHistories.Entity=CAST('Task' AS VARCHAR) AND EntityId=CAST(32 AS VARCHAR)

it's coming fine like this

Task- <b>T1</b> has been added by Swapnil Sharma
Task- <b>T1</b> status changed to <b>In Progress</b> by Swapnil Sharma<br/>

but now i want my above result # separated using stuff so i am using this

SELECT STUFF((SELECT '#' + ISNULL(CAST(TblActionHistories.Comments AS VARCHAR(MAX)),'') FROM TblActionHistories WHERE TblActionHistories.Entity=CAST('Task' AS VARCHAR) AND EntityId=CAST(32 AS VARCHAR) for xml path ('')),1,1,'')

it gives me this 

Task- &lt;b&gt;T1&lt;/b&gt; has been added by Swapnil Sharma#Task- &lt;b&gt;T1&lt;/b&gt; status changed to &lt;b&gt;In Progress&lt;/b&gt; by Swapnil Sharma&lt;br/&gt;

you can clearly see that all the special char. like < > converted to &lt; &gt; respectively

please help me out with this i want them to come in their original format

Upvotes: 3

Views: 11951

Answers (2)

rahul
rahul

Reputation: 7663

well i found the solution as suggested by IvanG 

SELECT STUFF((SELECT '#' + ISNULL(CAST(TblActionHistories.Comments AS VARCHAR(MAX)),'') FROM TblActionHistories WHERE TblActionHistories.Entity=CAST('Task' AS VARCHAR) AND EntityId=CAST(32 AS VARCHAR) for xml path(''), root('MyString'), type ).value('/MyString[1]','varchar(max)') ,1,1,'')

ref. to this article

http://blogs.lobsterpot.com.au/2010/04/15/handling-special-characters-with-for-xml-path/

Upvotes: 6

Ansari
Ansari

Reputation: 69

Try casting to NVARCHAR instead of VARCHAR

SELECT STUFF((SELECT '#' + ISNULL(CAST(TblActionHistories.Comments AS NVARCHAR(MAX)),'') FROM TblActionHistories WHERE TblActionHistories.Entity=CAST('Task' AS NVARCHAR) AND EntityId=CAST(32 AS VARCHAR) for xml path ('')),1,1,'')

Upvotes: -2

Related Questions