remove invalid character in table

Question

Post reply

remove invalid character in table

Yashshri

Old Hand

Points: 331
More actions
March 21, 2014 at 2:01 am

#100180

I have one table. I found some invalid characters in the table data. The column has data type nvarchar. when I try to find out all the data containing that special character (Like operation), it gives no result. I.e the special character is not recognized by the Sql server.
I want to remove that charatcter from the table data.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

Which character are we talking about?

Can you show us the query you wrote with the LIKE operator?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Yashshri Old Hand Points: 331 More actions · Answer 2

its something like ?

Query that I used is

select * from Customer where CustomerName like '%?%'

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

I think ? is a placeholder for a non-printable character, so you won't find it when you search for it.

Maybe you should inspect the data using a hex editor, to find out which hex value it has and then use the CHAR function inside a REPLACE to get rid of it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

KS Hall of Fame Points: 3326 More actions · Answer 4

If you can hold the column data in a variable for which you want to replace special characters the below might work

DECLARE @A NVARCHAR(100)

SELECT @A = 'John£?Smith'

;WITH Tally (Num) AS

(

SELECT TOP (LEN(@A))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM MASTER.DBO.SYSCOLUMNS AS S1

CROSS JOIN MASTER.DBO.SYSCOLUMNS AS S2

)

SELECT @A =

STUFF(@A,PATINDEX('%[^A-Z]%',@A),1,'')

FROM Tally

WHERE SUBSTRING(@A,Num,1) LIKE '[^A-Z]'

SELECT @A

Yashshri Old Hand Points: 331 More actions · Answer 5

Thanks everyone.

I tried converting nvarchar column to varhar and then applied search condition. and it worked!:-)

remove invalid character in table

Cookies on SQLServerCentral