Solved

Special Characters in SQL 2000

Posted on 2007-04-05
9
2,362 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

803 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