MSSQL - Convert speсial types to strings or HTML:MONEY to string, DATETIME to string, GUID or UNIQUEIDENTIFIER to string, VARBINARY or BINARY to string, XML to string, TIMESTAMP to string

Most of datatypes is relative simple convert to a string.
For this we can use the CONVERT() or even the CAST() function:

SELECT int_as_string = CONVERT(VARCHAR(20), int_field) FROM MyTable;

SELECT int_as_string = CAST(int_field AS VARCHAR(20))  FROM MyTable;

But for some other datatypes convert is not so simple.
For example for MONEY, DATETIME, UNIQUEIDENTIFIER, BINARY, XML and TIMESTAMP datatypes.

There the script which shows how to convert such datatypes to strings:

USE tempdb;
GO

-- Drop test table if exists
IF OBJECT_ID('test_converts') IS NOT NULL
    DROP TABLE test_converts;
GO

-- Create test table
-- The technique to generate random money value I have used from there:
--     http://yabele.blogspot.de/2013/08/mssql-random-number-generator-with.html
CREATE TABLE test_converts
(
    mon MONEY             NOT NULL DEFAULT RAND(CHECKSUM(NEWID())) * 1000,
    dt DATETIME           NOT NULL DEFAULT GETDATE(),
    guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    bin VARBINARY(MAX)    NOT NULL DEFAULT NEWID(),
    x XML                 NOT NULL,
    ts TIMESTAMP
);
GO

-- Fill test table with 3 records
-- For all columns except "x" (xml) the default values will be used
INSERT INTO test_converts (x)
VALUES
    ('<a b="c"><d/></a>'),
    ('<e f="g"><h/></e>'),
    ('<i j="k"><l/></i>');

-- Show original values
SELECT * FROM test_converts;

-- Convert values to string
SELECT
                          -- Third parameter (0) is default and generates result text
                          -- in "0.00" format
    money_as_string     = CONVERT(VARCHAR(20), mon, 0),
                          -- Third parameter (121) generates result text
                          -- in ODBC-canonical format: "yyyy-MM-dd HH:mm:ss.sss"
    datetime_as_string  = CONVERT(VARCHAR(30), dt, 121),
    guid_as_string      = CONVERT(VARCHAR(36), guid),
                          -- Third parameter (1) generates result text
                          -- in uppercase form with "0x" prefix
    binary_as_string    = CONVERT(VARCHAR(MAX), bin, 1),
    xml_as_string       = CONVERT(VARCHAR(MAX), x),
    timestamp_as_string = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ts), 1)
FROM
    test_converts;

After execution in SSMS (SQL Server Management Studio) we will have these two resultsets:



These techniques could be used for example to convert table-content to HTML.
In case you need convert values to HTML-string some converted string values will be necessary to HTML-encode:

-- Convert values to HTML-string
SELECT
                        -- Third parameter (0) is default and generates result text
                        -- in "0.00" format
    money_as_HTML     = CONVERT(VARCHAR(20), mon, 0),
                        -- Third parameter (121) generates result text
                        -- in ODBC-canonical format: "yyyy-MM-dd HH:mm:ss.sss"
    datetime_as_HTML  = CONVERT(VARCHAR(30), dt, 121),
    guid_as_HTML      = CONVERT(VARCHAR(36), guid),
                        -- Third parameter (1) generates result text
                        -- in uppercase form with "0x" prefix
    binary_as_HTML    = CONVERT(VARCHAR(MAX), bin, 1),
                        -- Convert XML-string to HTML-encoded string
    xml_as_HTML       = REPLACE(REPLACE(REPLACE(
                            CONVERT(VARCHAR(MAX), x)
                        , '&','&amp;'), '<', '&lt;'), '>', '&gt;'),
    timestamp_as_HTML = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ts), 1)
FROM
    test_converts;

Correspondent resultset in SSMS:

---

See also:
MSSQL - The random number generator with a random initialization (random seed)

MSSQL - Simple GROUP_CONCAT

The simple and compact form of GROUP_CONCAT functionality in SQL Server.

Re-Creates table with test strings:

-- Re-Create and fill table with test strings
IF OBJECT_ID('dbo.TestTexts') IS NOT NULL
    DROP TABLE dbo.TestTexts;
GO
CREATE TABLE dbo.TestTexts (SomeText VARCHAR(20));
GO
INSERT INTO dbo.TestTexts
VALUES
    ('Aaa'),
    ('Bbb'),
    ('Ccc'),
    ('Ddd');
GO

Simple concatination of strings:

SELECT GROUP_CONCAT = '' + (
    SELECT '' + SomeText
    FROM dbo.TestTexts
    ORDER BY SomeText
    FOR XML PATH('')
)

Query Result:

GROUP_CONCAT
------------
AaaBbbCccDdd

Concatinates Strings with delimiter:

SELECT
    -- Remove First Comma
    GROUP_CONCAT = STUFF(
        (
            -- Concatinate Strings
            SELECT ',' + SomeText
            FROM dbo.TestTexts
            ORDER BY SomeText
            FOR XML PATH('')
        ),
        1,1,''
    );
GO    

Query Result:

GROUP_CONCAT
---------------
Aaa,Bbb,Ccc,Ddd

MSSQL - Query Templates - GROUP_CONCAT

Today I will show how to implement aggregation of strings in MSSQL  (similar to MySQL GROUP_CONCAT). My favorite way is with usage of FOR XML expression.

Imagine that we have, such a database:



We need to build a list of table fields so, that if a field with the same name is in multiple tables, then the second column in result set will contain alphabetically sorted and comma separated set of table names. For example, in our case, for all fields terminating by '_ID':



To solve this task, we will use the data from INFORMATION_SCHEMA tables.
In the first step, we will get a list of COLUMN-TABLE combinations:

SELECT DISTINCT
    COLUMN_NAME,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    COLUMN_NAME, TABLE_NAME

now the same, but as XML:

SELECT DISTINCT
    COLUMN_NAME,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    COLUMN_NAME, TABLE_NAME
FOR XML RAW

Now for each field name we need get own list of tables. Result should be also in XML form.
For this we need first a list of unique field names:

SELECT DISTINCT
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    COLUMN_NAME

now combine this query with a query that returns list of COLUMN-TABLE combinations:

SELECT DISTINCT
    [COLUMN] = C1.COLUMN_NAME,
    [TABLEs] = (
            SELECT C2.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS C2
            WHERE
                C2.COLUMN_NAME = C1.COLUMN_NAME
            ORDER BY TABLE_NAME
            FOR XML RAW
        )
FROM INFORMATION_SCHEMA.COLUMNS C1
ORDER BY [COLUMN]

We obtain the following table:



It now remains:
- Filter out the unnecessary tables (sysdiagrams)
- Leaving only the fields that terminating by '_ID'
- Replace the start and end XML tags with delimiter (comma)

SELECT DISTINCT
    [COLUMN] = C1.COLUMN_NAME,
    [TABLEs] = REPLACE(REPLACE(REPLACE(
        (
            SELECT T=C2.TABLE_SCHEMA + '.' + C2.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS C2
            WHERE
                C2.TABLE_NAME != 'sysdiagrams'
                AND
                C2.COLUMN_NAME = C1.COLUMN_NAME
            ORDER BY 1
            FOR XML RAW
        ),
        '"/><row T="', ', '),
        '<row T="', ''),
        '"/>', '')
FROM INFORMATION_SCHEMA.COLUMNS C1
WHERE
    C1.TABLE_NAME != 'sysdiagrams'
    AND
    C1.COLUMN_NAME LIKE '%_ID'
ORDER BY [COLUMN]

The result of the query:



It remains to convert all of this in the form of T-SQL template:

USE <DataBaseName, sysname, tempdb>;
SELECT DISTINCT
    [COLUMN] = C1.COLUMN_NAME,
    [TABLEs] = REPLACE(REPLACE(REPLACE(
        (
            SELECT T=CASE C2.TABLE_SCHEMA
      WHEN SCHEMA_NAME()
      THEN ''
      ELSE C2.TABLE_SCHEMA + '.'
     END + C2.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS C2
            WHERE
                C2.TABLE_NAME != 'sysdiagrams'
                AND
                C2.COLUMN_NAME = C1.COLUMN_NAME
            ORDER BY T
            FOR XML RAW
        ),
        '"/><row T="', ', '),
        '<row T="', ''),
        '"/>', '')
FROM INFORMATION_SCHEMA.COLUMNS C1
WHERE
    C1.TABLE_NAME != 'sysdiagrams'
    AND
    C1.COLUMN_NAME LIKE <ColumnNamePattern, sysname, '%'>
ORDER BY [COLUMN]

Save it in the list of SQL-templates.
Now we can use this template to find the fields of the same name in different tables:

About templates and usage of the system tables you can read here:
MSSQL - Query Templates
MSSQL - Query Templates - usage of system tables

MSSQL - Convert list to table #2 (array of string items)

Often, when working with databases there is a need to pass a parameter with set of elements.
Function described here (TABLE-Value UDF) converts a string in the form "aaa;bbb;ccc" to tabular form.
Additionally could be removed empty items.
In case if list has non-unique items, the function returns the serial number for each items group (RANK).

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tf_array2table'))
    DROP FUNCTION dbo.tf_array2table;
GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to convert list of Values to Table
 
Usage: Get all items
    SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
    FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);
Usage: Get all non-empty items
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);
Usage: Get all non-empty items and filter non-unique
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
    WHERE item_rank=1;
=============================================*/
CREATE FUNCTION dbo.tf_array2table
(
    @array NVARCHAR(MAX), -- List of delimited values
    @delim NCHAR(1),      -- Delimiter
    @remove_empty BIT     -- Flag to remove empty values
)
-- Container for Array Items
RETURNS @data TABLE(
    item_index INT IDENTITY(1,1), -- 1-based index of item
    item_value NVARCHAR(MAX),     -- item value
    item_rank  INT                -- item rank if items are not unique
)
AS BEGIN
    -- Container for XML
    DECLARE
        @xml_text NVARCHAR(MAX),
        @xml XML;
 
    IF @remove_empty = 1 BEGIN
        -- Remove empty inner items
        WHILE (CHARINDEX(@delim + @delim, @array, 0) > 0) BEGIN
            SET @array = REPLACE(@array, @delim + @delim, @delim);
        END;
        -- Remove empty left item
        IF(LEFT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 2, LEN(@array)-1)
        END;
        -- Remove empty right item
        IF(RIGHT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 1, LEN(@array)-1)
        END;
    END;
    
    -- Prepare XML-Text
    SET @xml_text = N'<L><I>' +
        REPLACE(@array, @delim, N'</I><I>') +
        N'</I></L>';
    
    -- Convert Array to XML
    SET @xml = CAST(@xml_text AS XML);
 
    DECLARE @temp_data TABLE(
        item_index INT IDENTITY(1,1),
        item_value NVARCHAR(MAX)
    )
    -- Extract Array Items to Table-Variable
    INSERT INTO @temp_data(item_value)
    SELECT
        item_value = item.value('.', 'NVARCHAR(MAX)')
    FROM
        @xml.nodes('//I') XMLDATA(item);
    
    -- Calculate Rank for each item (to find non-unique items)
    INSERT INTO @data(item_value, item_rank)
    SELECT
        item_value,
        item_rank=RANK() OVER(PARTITION BY item_value ORDER BY item_index, item_value)
    FROM
        @temp_data
    ORDER BY
        item_index;
    
    RETURN;
END;
GO

Usages:

-- Get all items
SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);

Result

item_index	item_value	item_rank
1	---	1
2	ddd	1
3	bbb	1
4	ccc	1
5	---	2
6	---	3
7	ccc	2
8	ddd	2
9	eee	1
10	---	4

-- Get all non-empty items
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
4	ccc	2
5	ddd	2
6	eee	1

-- Get all non-empty items and filter non-unique
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
WHERE item_rank=1;

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
6	eee	1

MSSQL - Convert list to table #1 (array of numbers in string form)

Extended version of function from this post: MSSQL - Convert list to table #2 (array of string items)
Function described here (TABLE-Value UDF) converts a string in the form "123;bbb;;;456" to tabular form.
Result will contain only non-empty items which was possible to convert to INTEGER.
In case if list has non-unique items, the function returns the serial number for each items group (RANK).

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.fnArray2IntTable'))
    DROP FUNCTION dbo.fnArray2IntTable;
GO

/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to convert list of numeric Values to Table of integers
Remark:
    Empty and non-numeric values will be ignored

Usage: Get all items
    SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';');
Usage: Get all non-empty items and filter non-unique
    SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';') WHERE ItemRank=1;
=============================================*/
CREATE FUNCTION dbo.fnArray2IntTable(
    @Array NVARCHAR(MAX),
    @Delim NCHAR(1)
)
-- Container for Array Items
RETURNS @Data TABLE(
    ItemIndex INT IDENTITY(1,1),
    ItemValue INT,
    ItemRank INT
)
AS BEGIN
    -- Container for XML
    DECLARE
        @XmlText NVARCHAR(MAX),
        @Xml XML;
    
    -- Remove empty inner items
    WHILE (CHARINDEX(@Delim + @Delim, @Array, 0) > 0) BEGIN
        SET @Array = REPLACE(@Array, @Delim + @Delim, @Delim);
    END;
    -- Remove empty left item
    IF(LEFT(@Array, 1) = @Delim) BEGIN
        SET @Array = SUBSTRING(@Array, 2, LEN(@Array)-1)
    END;
    -- Remove empty right item
    IF(RIGHT(@Array, 1) = @Delim) BEGIN
        SET @Array = SUBSTRING(@Array, 1, LEN(@Array)-1)
    END;
    
    -- Prepare XML-Text
    SET @XmlText = N'<List><Item>' +
        REPLACE(@Array, @Delim, N'</Item><Item>') +
        N'</Item></List>';
    
    -- Convert Array to XML
    SET @Xml = CAST(@XmlText AS XML);
    
    -- Temp Table-Variableble
    DECLARE @TempData TABLE(
        ItemIndex INT IDENTITY(1,1),
        ItemValue NVARCHAR(MAX)
    )

    -- Extract Array Items to temp Table-Variable
    INSERT INTO @TempData
    SELECT
        Item = item.value('.', 'INT')
    FROM
        @Xml.nodes('//Item') XMLDATA(item)
    WHERE
        -- Skeep non-numeric items
        ISNUMERIC(item.value('.', 'NVARCHAR(MAX)')) = 1;
    
    -- Calculate Rank for each item (to find non-unique items)
    INSERT INTO @Data(ItemValue, ItemRank)
    SELECT
        ItemValue,
        ItemRank=RANK() OVER(PARTITION BY ItemValue ORDER BY ItemIndex, ItemValue)
    FROM
        @TempData
    ORDER BY
        ItemIndex;

    RETURN;
END;

GO

-- Get all non-empty and numeric items
SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';');
-- Get all non-empty and numeric items and filter non-unique
SELECT * FROM dbo.fnArray2IntTable(N';444;bbb;333;;;333;444;555;', N';') WHERE ItemRank=1;

Result of execution: