how to update particular column query not working

Question

1.00/5 (1 vote)

See more:

In a column(name :hadith_eng_book) I have unicode and English Text.
Accidentally unicode text have copied to Column(hadith_eng_book).

Now I want to replace all unicode text with english text.
look at here
hadith_eng_book column
values are
Sunan Ibn Majah
Sunan Ibn Majah
Sunan Ibn Majah
Sunan Ibn Majah
سنن ابن ماجہ this is unicode i dont want, I want Sunan Ibn Majah text here like above
سنن ابن ماجہ
سنن ابن ماجہ

question is simple
I use this query but when I execute it. show me 0 rows effected

SQL

update [SSDB].[dbo].[Hadiths_old]
 set hadith_eng_book ='Sunan Ibn Majah'
 where hadith_eng_book= 'سنن ابن ماجہ';

i want to replace سنن ابن ماجہ with Sunan Ibn Majah.

now how to update ?

Posted 30-Sep-13 21:55pm

Muhamad Faizan Khan

Updated 30-Sep-13 22:04pm

v2

Add a Solution

Comments

Maciej Los 1-Oct-13 3:58am

Maybe question is simple, but completely not understandable ;(

Muhamad Faizan Khan 1-Oct-13 4:01am

question have improved

Azee 1-Oct-13 4:21am

Hey Faizan, what is the datatype of hadith_eng_book Column in your table?

Muhamad Faizan Khan 1-Oct-13 11:57am

hadith_eng_book datatype is nvarchar(50)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-09-30T22:08:00

First of all, please, read my comment to the question.

Try to use:

SQL

DECLARE @NewText NVARCHAR(30)
SET @NewText= N'Sunan Ibn Majah'

DECLARE @OldText NVARCHAR(30)
SET @OldText = N'سنن ابن ماجہ'

update [SSDB].[dbo].[Hadiths_old]
 set hadith_eng_book =@NewText
 where hadith_eng_book= @OldText

MSDN TechNet wrote:
Many code examples prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

For furhter information, please see: Transact-SQL Syntax Conventions (Transact-SQL)[^]

Kamran Ayati · Answer 2 · 2013-09-30T21:57:00

Solution 1

you should use a simple programm to update it from out of sql

first proccess string and convert it to good format then write or update to file or sql

Posted 30-Sep-13 21:57pm

Kamran Ayati