Special Characters in SQL 2000

I am  having a problem with
some text values (names and descriptions) that use special characters not in the normal ASCII or UTF-8 character set. One example of this was an employee name of “Kühn I.”. Most (if not all) of our character strings in tables and stored procedures are varchar not nvarchar. Do you know of anyway to allow the special characters without making all the changes required to go from varchar to nvarchar?
LVL 11
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
Hi,
Any one have any thoughts.................
0
Sham HaqueSenior SAP CRM ConsultantCommented:
you could try this to translate them into acceptable ASCII:

http://novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-50-asciichars.htm
0
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
I don't want to modify the text value ....because the function removing the special character.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
I want to allow special character without any changes.......
0
Brain2000Commented:
A way to store special characters like this is to make sure that the collition matches the types of characters that you are going to be storing.  I believe that collition 1252 allows for storing characters with umlauts (sp?).  But if you get into other types of special characters, such as Japanese, Arabic, special Russian characters, you will need to either set those codepages, or you will have to use nvarchar (unicode).  The good news is, you can change varchar to nvarchar without any issues, unless you have varchar's larger than 4000 characters.  The max limit is 8000 for varchar, 4000 for nvarchar.  The collition will also assure that the ORDER BY clauses return things in the right order.

One final thing to remember, if you use UTF-8 encoding, SQL server will not sort or return the length of fields correctly.

Why do you not want to turn some of the fields into nvarchar if they are less than or equal to 4000 characters in size?
0
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
I am not going to modify fields with nvarchar because it make huge changes and can effect the issue in every place.

so i am looking for the solution which allow me to use special character without modification of the schema changes as well as SP/view..etc.

Can some setting  database level allow me this task...???
0
Brain2000Commented:
I'm not sure what you mean by "effect the issue in every place."  nvarchar should not cause any issue anywhere, except that it takes up more space, and limits you to 4000 characters instead of 8000.

The only ways I know how to store foreign characters are:

1) Set the collation page to the specific fields, or the entire database
2) Manually store UTF-8 data (which causes length functions to miscalculate)
3) Change the fields to nvarchar.

But as I said, the umlaut in "Kühn" should store just fine in a 1252 codepage, so I'm not sure why it didn't work in the first place.

It is possible the application that you are using is not handling characters above 127?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.