Solved

Special Characters in SQL 2000

Posted on 2007-04-05
9
2,375 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CASE Statement for unknown # of columns 10 65
SQL State HYT00. Timeout expired proplem 8 37
Datatable / Dates ? 4 25
Rewriting a simple query 2 26
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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 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