How to Remove Hex Unicode Characters from NVARCHAR field

Other SQL Server Topics
1

I imported a database from Access to Sql Server Express, and one of the fields contains various Hex Unicode characters which is causing an error in my php file, so I would like to remove the characters from the field. The field type in sql server is nvarchar(max).

The values are:

& # x 9 ;
& # x A ;
& # x D ;

*inserted spaces between characters to display.

I haven't been able to find a way to remove these. The replace function doesn't work on it.

Thanks for any help!

2

Welcome

Could you please show us what you did with replace function that did not work

3

update Standards_T set wording = replace(wording, '& # x 9 ;','')

*I have to put spaces in the characters or they won't show here

4

What you have here seems that it should work.

update Standards_T set wording = replace(wording, '	','')

So this is the original hex data in your table that you want to remove

	




Works for me?

image
image726×627 43.4 KB

5

Unfortunately, that doesn't work for me. The only way I can see the values in the db is to run this query.

SELECT * FROM Standards_T FOR XML AUTO, TYPE

I found a way around it in my PHP file, but it's really not the way I should be fixing it.

Thanks for your help.

6

and what does it look like when you do that xml auto stuff? confidential data obfuscated. it does sound like in access that column was xml style data?

7

<Standards_T ID="2036" Code="MLR-Science.6-8.D.3.i" Grade="24" Content_Area="3" Content_Area_Subset="51" Skill_Set="178" Wording="& # x 9;Use examples of energy transformations from one form to another to explain that energy cannot be created or destroyed." />

8

i think there is something missing here. you say the column holding this data is nvarchar(max) ? what does it look like when you just do a raw query?

9

It will just come back as "Use examples of energy transformations from one form to another to explain that energy cannot be created or destroyed." without the leading characters. Yes, it's nvarchar(max).

10

without the leading hex because you removed it to show us what you have or ?

11 
&#x9;

please add three ticks in front and at the end of the data you post to show us exactly what it is you are seeing.

the tick for posting the data is the following

=> ` <=

image