One of the greatest challenges when designing and accessing database information is turning data into information and vice-versa, as well as deciding to what extent you want to manipulate your information to make more efficient or more secure data. For smaller data bases it doesn't make much sense to convert all of your string data into numeric data but if you have large amounts of rows to be stored it can be much more efficient for your query's to return large amounts of numeric data and then convert the rows you need to view or change into string data, or meaningful information. It is also more secure to store passwords or sensitive information as numeric data so that in the case of a leak of data, the data is less meaningful to unknowing eyes.
There are 4 similar functions used in T-SQL for the parsing, replacement, and manipulation of character values: ASCII(), CHAR(), UNICODE(), and NCHAR(). The functions differ in the industry standards to which they comply (ASCII() and CHAR() to ASCII; UNICODE() and NCHAR() to Unicode standards.)
The American Standard Code for Information Interchange (ASCII) standard is a 128 charachter set of alpha, numeric, and punctuation characters. Although this standard is somwhat outdated it was central to the IBM PC architecture, and is still highly used in modern computing. The ASCII() function is used to convert string data into its corresponding ASCII value; 0-127. The opposite, or converting numeric data into string data, is performed with the CHAR() function.
ex:
returns 65
returns the letter A
The Unicode standard was established in order to support all printable languages. Although it takes 2 bytes of storage per character (ASCII only takes 1 byte) there are more than 65,000 supported characters. The UNICODE() function is the equivalent of the ASCII() function except it converts our character to its corresponding Unicode number. The NCHAR() function for Unicode is the same as the CHAR() function for the ASCII set. These are used just the same as the above ones.
Depending on you purposes and the need for multi language support within your program either one can be useful. The actual implamentation of the functions is pretty simple but always a good skill to have if you are dealing with large amounts of data in SQL.
There are 4 similar functions used in T-SQL for the parsing, replacement, and manipulation of character values: ASCII(), CHAR(), UNICODE(), and NCHAR(). The functions differ in the industry standards to which they comply (ASCII() and CHAR() to ASCII; UNICODE() and NCHAR() to Unicode standards.)
The American Standard Code for Information Interchange (ASCII) standard is a 128 charachter set of alpha, numeric, and punctuation characters. Although this standard is somwhat outdated it was central to the IBM PC architecture, and is still highly used in modern computing. The ASCII() function is used to convert string data into its corresponding ASCII value; 0-127. The opposite, or converting numeric data into string data, is performed with the CHAR() function.
ex:
- SELECT ASCII ('A')
SELECT ASCII ('A')
returns 65
- SELECT CHAR(65)
SELECT CHAR(65)
returns the letter A
The Unicode standard was established in order to support all printable languages. Although it takes 2 bytes of storage per character (ASCII only takes 1 byte) there are more than 65,000 supported characters. The UNICODE() function is the equivalent of the ASCII() function except it converts our character to its corresponding Unicode number. The NCHAR() function for Unicode is the same as the CHAR() function for the ASCII set. These are used just the same as the above ones.
Depending on you purposes and the need for multi language support within your program either one can be useful. The actual implamentation of the functions is pretty simple but always a good skill to have if you are dealing with large amounts of data in SQL.
TrackBack URI for this entry
Subscribe to this comment's feed
