Very frequently we need to use the data type UTF-8 to store a data in different languages. As described in the MSDN page the UTF-8 data type is stored in the ‘Samples’ folder in SQL Server (Server\100\Samples\Engine\Programmability\CLR\UTF8String). You just need to activate it.


Create data type UTF-8

1. Generate a strong name key file
1.1. Open Command Prompt (Start –> Run –> ‘cmd’)
1.2. Change directory (C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin)
1.3. Generate the key (C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin>sn -k “C:\Program Files\Microsoft SQL Server\100\Samples\SampleKey.snk”)

2. Build the sample
2.1. Open Microsoft Visual Studio
2.2. Open ‘C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\UTF8String\CS\UTF8String.sln’
2.3. Build –> Build UTF8String

3. Install UTF-8 data type
3.1. Open SQL Server Management Studio
3.2. Open ‘C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\UTF8String\Scripts\InstallCS.sql’
3.3. Execute it
3.4. Test UTF-8 data type (C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\UTF8String\Scripts\Test.sql)
3.4.1. Fix the error message ‘Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.‘

sp_configure 'clr enabled', 1;
RECONFIGURE;

3.4.2. Test UTF-8

DECLARE @u Utf8String;
SET @u = CONVERT(Utf8String, 'hello world');
SELECT
	@u.ToString()
	, CONVERT(varbinary(8000), @u)
	, SUBSTRING(@u.ToString(), 1, 5)
	, @u.Utf8Bytes;

The result

Create PHP form to insert a UTF-8 data

1. Create table

USE zlika;

CREATE TABLE CharsetTest
(
	[Language] VARCHAR(50)
	, [Phrase] UTF8String
);

2. Create PHP form (insert.php)

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Charset Test</title>
</head>
<body>

<?php
// Connect to SQL Server
$server = 'ZLIKA';
$username = 'zlika';
$password = 'myPassword';
$connect = mssql_connect($server, $username, $password) or die ('No server connection!');
$sqldb = mssql_select_db('zlika', $connect) or die ('No database connection!');

// Get the variables
$language = $_POST['language'];
$phrase = $_POST['phrase'];

// Insert records in the database
$query = "INSERT INTO CharsetTest (Language, Phrase) SELECT '" . $language . "', '" . $phrase . "'";
$result = mssql_query($query);

// Close database connection
mssql_close($connect);
?>

<form action="insert.php" method="post">
	Language: <input name="language" /><br />
	Phrase: <input name="phrase" /><br />
	<input type="Submit" />
</form>

</body>
</html>

3. Insert test data

Bulgarian –> Честита Нова Година!
Arabic –> سنة جديدة سعيدة!
Armenian — > Շնորհավոր Նոր Տարի
Azarbaijani –> Yeni iliniz mübarək
Catalan –> Feliç Any Nou!
Chinese (Traditional) –> 新年快樂
Polish –> Szczęśliwego Nowego Roku!
Thai –> สวัสดีปีใหม่
hindi –> नया साल मुबारक हो

4. Check the content of the table (check.php)

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Charset Test</title>
</head>
<body>

<?php

// Connect to SQL Server
$server = 'ZLIKA';
$username = 'zlika';
$password = 'myPassword';
$connect = mssql_connect($server, $username, $password) or die ('No server connection!');
$sqldb = mssql_select_db('zlika', $connect) or die ('No database connection!');

// Select the data
$query = 'SELECT Language, Phrase.ToString() AS Phrase FROM CharsetTest';
$result = mssql_query($query);

//Retrieving data Header
echo '<table cellspacing="0" cellpadding="2" width="100%"><tr>';

$i = 0;
while ($i < mssql_num_fields($result))
{
	echo '<th>'. mssql_field_name($result, $i) . '</th>';
	$i++;
}
echo '</tr>';

//Retrieving rows
while ($row = mssql_fetch_array($result, MSSQL_ASSOC))
{
	echo '<tr>';
	foreach ($row as $data)
	{
		echo '<td>' . $data . '</td>';
	}
	echo'</tr>';
}

echo '</table>';

// Close database connection
mssql_close($connect);

?>

</body>
</html>

The result:

The result

Félicitations