/*=====================================================================

  File:      Test.sql for UTF8 SQLCLR Example
  Summary:   Demonstrates and tests a SQLCLR based UDT for storing UTF8 encoded strings.
  Date:		 March 31, 2004

---------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
======================================================= */

USE AdventureWorks;
GO

-- simple usage of the type in variables and select statements
-- convert to string, binary, do a substring on it, get the utf8bytes
DECLARE @u Utf8String;
SET @u = CONVERT(Utf8String, 'hello world');
SELECT @u.ToString(), 
    CONVERT(varbinary(8000), @u),
    SUBSTRING(@u.ToString(), 1, 5),
    @u.Utf8Bytes;
GO

-- create a table with this type
CREATE TABLE utf8test(u Utf8String);
GO

-- populate it with some random data
SET NOCOUNT ON;
BEGIN TRAN;
DECLARE @index int, @str nvarchar(4000);
SET @index = 0;
WHILE @index < 1000
BEGIN
    SET @str = CONVERT(nvarchar(100), @index);
    INSERT INTO utf8test VALUES(CONVERT(Utf8String, @str))
    SET @index = @index + 1;
END
COMMIT TRAN;
GO

-- Find a particular UTF8 string

DECLARE @i int;
SELECT @i = COUNT(*) from utf8test 
WHERE u = CONVERT(Utf8String, '100');
SELECT @i;
go

-- Find UTF8 strings with a particular substring
DECLARE @i int;
SELECT @i = COUNT(*) from utf8test 
WHERE SUBSTRING(u.ToString(), 1, 1) = '1';
SELECT @i;
GO


-- Add a computed column over the ToString method
ALTER TABLE utf8test ADD ustr AS u.ToString() PERSISTED;
GO

-- Create an index over the computed column
CREATE INDEX IX_utf8test_ustr ON utf8test(ustr);
GO

-- Binary comparisons on Utf8Strings are not effective.  Even if we use the ToString method
-- to convert it to a string, we may want to have more control over whether case, width, or
-- attributes are relevant to the comparison.  This portion of the test creates a table of 
-- localized data pulled from the resumes stored in the AdventureWorks database,
-- and then demonstrates comparsions and sorting based on the CompareUsingCulture 
-- and GetSortKeyUsingCulture methods defined on Utf8String.


IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ResumeFullName') AND ([type] = 'FN'))
DROP FUNCTION [dbo].[ResumeFullName];
GO

-- Returns the complete name of the applicant from her resume.  
-- We don't just use data(/RES:Resume[1]/RES:Name) because we need spaces
-- between each part of the name.
CREATE FUNCTION ResumeFullName (@Resume xml)
RETURNS nvarchar(100)
AS
BEGIN
    RETURN CONVERT(nvarchar(100), @Resume.query(N'declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; for $b in /RES:Resume[1]/RES:Name/* return data($b)'));
END
GO

SELECT Cast(RTRIM(LTRIM(dbo.ResumeFullName(Resume))) as Utf8String) AS FullName 
INTO ResumeNames FROM HumanResources.JobCandidate;
GO

SELECT FullName.ToString() FROM ResumeNames;

DECLARE @Shai Utf8String;
SET @Shai = N'Shai  Bassli';
SELECT FullName.ToString() FROM ResumeNames 
WHERE FullName.CompareUsingCulture(@Shai, 'en-us', 1, 0, 0) = 0;
GO

ALTER TABLE ResumeNames ADD SortKey AS FullName.GetSortKeyUsingCulture('en-us', 1, 0, 0) PERSISTED;
GO

CREATE INDEX IX_ResumeNames_SortKey ON ResumeNames(SortKey);
GO

SELECT FullName.ToString() 
FROM ResumeNames
ORDER BY SortKey;