Check if field contains special character in SQL

问题:

We decided to use Nvarchar to store some information in some tables, the reason is that we assumed that we will have a lot of special characters since the database contains French and German data.

After extracting some of the data, we estimated the full run to have and extreamlly huge size (20 TB)

Now we would like to check every table to find if there are special characters found, if not then we change the type from NvarChar to Varchar

Any idea how can we query this?

or what do you think the best way to optimize the size?

回答1:

Had to solve this a little while ago myself:

Use regex:

LIKE '%[^a-zA-Z0-9]%'

To solve the problem of searching the tables, try: How do I find a value anywhere in a SQL Server Database? , if not I got a better one somewhere that I use.

回答2:

Actually a much better idea is to use a compressed table. SQL Server 2008 has page compression and SQL Server 2008 R2 adds Unicode compression on top of that.