ansaurus

Question

Answer 1

+2 A:

I am storing them in a varchar field ...

There's one problem, at least. For text that could have "special" characters, you need nvarchar.

Joel Coehoorn 2009-12-02 21:58:15

+1 Also it should be noted that it's unless you're expecting to allow "special characters" or "foreign language characters", you'll probably want to stick with a varchar especially if you're going to index by that column. For one thing varchars take up half as much space as nvarchars, and for another thing nvarchar query plans use a scan, whereas varchars use a seek. Seek is much faster than scan. If that makes any sense.

blesh 2009-12-02 22:04:56

What do I need nvarchar?

Mike C. 2009-12-02 22:22:45

varchar only allows a single byte per character. "special" characters often require more than that.

Joel Coehoorn 2009-12-02 22:24:34

But if I use the codes like © I will be fine with a varchar, right?

Mike C. 2009-12-02 23:01:06

Maybe for a while, but eventually someone will try to put unicode characters into your app. best to be prepared.

Joel Coehoorn 2009-12-03 02:55:06

Depends on your RDBMS. Some of them support UTF-8-encoded varchars. I don't think MS SQL Server does, though.

dan04 2010-03-09 04:42:37

@dan04 Sql Server definitely does support UTF-8 (it just doesn't call it that)

Joel Coehoorn 2010-03-09 14:39:47

Answer 2

+2 A:

Well, with those characters, generally you will render them as

&copy;

So you don't need to do anything special in the DB, but you should be using the "N"-prefixed fields for DB strings, NChar, NVarChar, and so on.

Noon Silk 2009-12-02 22:00:09

Could you expand a little on the "N"-prefixed fields?

IrishChieftain 2009-12-02 22:12:22

I'm just referring to these: http://msdn.microsoft.com/en-us/library/aa276823%28SQL.80%29.aspx

Noon Silk 2009-12-02 22:14:07

Why should I use nvarchar?

Mike C. 2009-12-02 22:23:28

Well, you need them if you want to hold international characters; i.e. a site/system that supports multiple languages, which most (significantly advanced) systems need to do; so it's a good precaution, IMHO.

Noon Silk 2009-12-02 22:25:29

Thanks Silky - I may start a new question, but will these help me to cater for apostrophes and ampersands?

IrishChieftain 2009-12-02 22:25:36

IrishChieftain: No need for those ones; they will fit in a regular "char" field.

Noon Silk 2009-12-02 22:28:22

Answer 3

A:

There's no need to switch to nvarchar unless you have an explicit requirement to support Unicode.

RickNZ 2009-12-03 05:36:55

Great, thanks for the advice. I suppose this can cause difficulties if the same text field that contains these HTML entities is used in a windows applications instead of a web application.

Mike C. 2009-12-03 14:21:18

Sure, depending on how the text is displayed. You could always use a browser window embedded in your Windows app, if you need to.

RickNZ 2009-12-03 15:02:35

Why not just store the data as-is and convert it to HTML or JavaScript escapes as needed?

dan04 2010-03-09 04:40:44

@dan04: if varchar can be used instead of nvarchar, the associated data will only occupy half the amount of space in the database. Also, the question was for an ASP.NET web page, in which case it's generally easier and more reliable to fixup the data once on write than to make sure that it's properly converted to escapes every place it's read.

RickNZ 2010-03-09 20:42:20

ansaurus

tags:

views:

answers:

Storing special characters in database

related questions