March 21, 2014 at 2:01 am
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.
March 21, 2014 at 2:05 am
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
March 21, 2014 at 4:25 am
its something like ?
Query that I used is
select * from Customer where CustomerName like '%?%'
March 21, 2014 at 4:39 am
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
March 21, 2014 at 7:25 am
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
March 25, 2014 at 2:15 am
Thanks everyone.
I tried converting nvarchar column to varhar and then applied search condition. and it worked!:-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply