Systems Engineering and RDBMS

Archive for October 13th, 2007

Some more String Functions

Posted by decipherinfosys on October 13, 2007

In some of our previous posts, we have covered different types of functions (String functions, mathematical functions, datetime functions etc.) that exist in Oracle, SQL Server and DB2 LUW. You can search for functions on our blog site and get to those posts. In this post, we will cover some other functions which do not get used that often but it is important to know them for that corner case situation when you might happen to need them.

ASCII: This function takes the leftmost character of the string that is passed to it and returns the ASCII value fot it. Example: If we break the DECIPHER word:

SELECT ASCII(‘D’), ASCII(‘E’), ASCII(‘C’), ASCII(‘I’), ASCII(‘P’), ASCII(‘H’), ASCII(‘E’), ASCII(‘R’)

you will get the corresponding ASCII values:

———– ———– ———– ———– ———– ———– ———– ———–
68 69 67 73 80 72 69 82

And if you need to re-convert the number values back to a string, you would need to use the CHAR() function:

SELECT CHAR(68), CHAR(69), CHAR(67), CHAR(73), CHAR(80), CHAR(72), CHAR(69), CHAR(82)

would yield:

—- —- —- —- —- —- —- —-
D E C I P H E R

Likewise, one can use the UNICODE() function to return the UNICODE integer value for the first character of the string and then we can use the NCHAR() function to convert those UNICODE integer values back to their string equivalent, Example:

SELECT UNICODE(‘T’), UNICODE(‘E’), UNICODE(‘S’), UNICODE(‘T’)

———– ———– ———– ———–
84 69 83 84

and for converting these back to the string:

SELECT NCHAR(84), NCHAR(69), NCHAR(83), NCHAR(84)

—- —- —- —-
T E S T

Posted in SQL Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers