SQL Server UNICODE Function


By:

The UNICODE function returns the Unicode standard value of the character used in the function. Unicode is a standard for the encoding representation in the writing system. Basically, it is a universal way to represent characters from around the world.

Syntax

UNICODE(expression)

Parameters

  • expression - this is the character used to get the UNICODE number.

Simple UNICODE Example

The following example will show the UNICODE of the character "υ".

SELECT UNICODE(N'υ') as code
UNICODE T-SQL simple example

Note that if several characters are used as input, the UNICODE function will only return the code of the first letter which is "a".

SELECT UNICODE(N'aei') as code
unicode get first letter only

UNICODE with NULL values

UNICODE will return NULL if a NULL value is used.

SELECT UNICODE(NULL) as code
unicode with NULL values

Convert from UNICODE to NCHAR

The following example will convert the UNICODE to a character.

SELECT NCHAR(UNICODE('&')) as character
convert UNICODE to char

Getting UNICODE from a Table Column

The following example will show the UNICODE of the first letter of column AddressLine1.

SELECT UNICODE(AddressLine1) as msg
FROM [Person].[Address]
UNICODE in a SQL Server table

Show the List of UNICODE Values of a String

The following example will create a function to return all the UNICODE values of a string separated by commas.

CREATE FUNCTION showUnicode( @string NVARCHAR(100))
returns varchar(500)
AS
BEGIN
   DECLARE @length smallint = LEN(@string)
   DECLARE @position smallint = 0
   DECLARE @codes varchar(max) = ''
 
   WHILE @length >= @position
   BEGIN
      SELECT @codes = @codes + CONCAT(UNICODE(SUBSTRING(@string,@position,1)),',')
      SELECT @position = @position + 1
   END
 
   SELECT @codes = SUBSTRING(@codes,2,LEN(@codes)-2)
   RETURN @codes
END

Next, we will execute the function.

 SELECT dbo.showUnicode('hello world') as codes
UNICODE T-SQL code separated by commas

Here is how we can use this function to get the data from a table.

SELECT FirstName, dbo.showUnicode('FirstName') as codes
FROM [Person].[Person]
UNICODE T-SQL code separated by commas

Get List of All NCHAR Values and Integer Value

The following will get a list of the UNICODE values and the corresponding Unicode character.

DECLARE @counter INT = 0
 
CREATE TABLE #NCharValues ([nchar] nchar(1), [unicode] int)
 
WHILE (@counter <= 144697)
BEGIN
   BEGIN TRY
      INSERT INTO #NCharValues
      SELECT NCHAR(@counter), UNICODE(NCHAR(@counter)) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter > 144697
      BEGIN
         BREAK
      END
   END CATCH
END
 
SELECT [UNICODE], [nchar] FROM #NCharValues
 
DROP TABLE #NCharValues

Here are a few rows from the query results.

unicode table listing

Related Articles


Last Update: 11/12/2021




Comments For This Article





download














vote


get free sql tips
agree to terms