[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Special Characters in SQL 2000

Posted on 2007-04-05
9
Medium Priority
?
2,385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

650 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