Solved

Special Characters in SQL 2000

Posted on 2007-04-05
9
2,369 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:Ved Prakash Agrawal
  • 4
  • 2
9 Comments
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18857524
Hi,
Any one have any thoughts.................
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18857558
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
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18857744
I don't want to modify the text value ....because the function removing the special character.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18857766
I want to allow special character without any changes.......
0
 
LVL 8

Expert Comment

by:Brain2000
ID: 18870248
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
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
ID: 18874607
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
 
LVL 8

Accepted Solution

by:
Brain2000 earned 500 total points
ID: 18879158
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question