MSSQL - T-SQL equivalent of VBA VAL() function

There the text of scalar-value function to extract numbers from string and convert to integer:

IF OBJECT_ID(N'dbo.fn_extract_digits') IS NOT NULL
  DROP FUNCTION dbo.fn_extract_digits;
GO

CREATE FUNCTION dbo.fn_extract_digits (@str VARCHAR(MAX))
/*
https://yabele.blogspot.com/2017/07/mssql-t-sql-equivalent-of-vba-val.html
*/
RETURNS INT
AS
BEGIN
  DECLARE
    @newstr VARCHAR(MAX) = '',
    @i INT = 1,
    @chr CHAR(1);

  WHILE @i <= LEN(@str) BEGIN
    SET @chr = SUBSTRING(@str, @i, 1);
    IF @chr BETWEEN '0' AND '9'
      SET @newstr = @newstr + @chr;
    SET @i = @i + 1;
  END;

  RETURN CAST(@newstr AS INT);
END
/*
DECLARE @str VARCHAR(MAX) = '1-23-456';
SELECT Digits = dbo.fn_extract_digits(@str);*/
;

MSSQL - Function to generate empty rows

There the text of inline table-value function to generate a specified count of empty rows:

IF OBJECT_ID(N'dbo.GenerateRows') IS NOT NULL
  DROP FUNCTION dbo.GenerateRows;
GO
CREATE FUNCTION dbo.GenerateRows (@Count INT)
/*
https://yabele.blogspot.com/2017/07/mssql-function-to-generate-empty-rows.html
*/
RETURNS TABLE
AS
RETURN
WITH TenRows AS (
  SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
)
SELECT TOP(@Count)
  I = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM
  TenRows T1 CROSS JOIN TenRows T2 CROSS JOIN TenRows T3 CROSS JOIN TenRows T4 CROSS JOIN TenRows T5
/*
SELECT * FROM dbo.GenerateRows(100);
*/
;

MSSQL - How to generate range of Dates

There the text of VIEW to generate range of dates between 1.January 2000 and 31.December next year:

IF OBJECT_ID(N'dbo.calendar_dates') IS NOT NULL
  DROP VIEW  dbo.calendar_dates;
GO
/*
http://yabele.blogspot.de/2016/08/how-to-generate-range-of-dates.html
*/
CREATE VIEW dbo.calendar_dates
AS
WITH
  R10 AS (
    SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
  )
SELECT TOP(DATEDIFF(DAY, '2000-01-01', CAST(YEAR(GETDATE()) + 1 AS CHAR(4)) + '-12-31'))
  date_value = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT 1)), '1999-12-31')
FROM
  R10 T1 CROSS JOIN R10 T2 CROSS JOIN R10 T3 CROSS JOIN R10 T4 CROSS JOIN R10 T5 CROSS JOIN R10 T6
/*
SELECT *
FROM dbo.calendar_dates
ORDER BY date_value;
*/
;

MSSQL - Performance tuning of Scalar-Value User Defined Functions (UDFs)

In most of cases the scalar-value User Defined Functions (Scalar UDF) are very slow.
It is enough to move the scalar expression in to UDF and query performance will be several times reduced.
Let us check ...

For testing, we need a test table with the dates from about a thousand years:

-- Recreate test table
IF OBJECT_ID('dbo.TestUDFs') IS NOT NULL
    DROP TABLE dbo.TestUDFs;
GO
CREATE TABLE dbo.TestUDFs(
    Datum Date
);
GO
-- SET NOCOUNT ON to prevent extra log messages returned as part of the result set:
-- (See: https://msdn.microsoft.com/en-us/library/ms189837%28v=sql.110%29.aspx)
SET NOCOUNT ON;
-- Fill test-table
DECLARE
    @Date Date = GetDate(),
       @i INT  = 0;
WHILE @i < 1000 * 365 BEGIN
    SET @Date = DATEADD(day, 1, @Date);
    INSERT INTO dbo.TestUDFs VALUES (@Date);
    SET @i = @i + 1;
END;
-- restore NOCOUNT
SET NOCOUNT OFF;
GO
SELECT CNT = COUNT(*) FROM dbo.TestUDFs;
GO

---

We will create four different UDFs:

FIRST:
Scalar-value UDF, which uses variables for intermediate calculations.
The body of function is placed into BEGIN-END container.

-- Recreate scalar-value UDF with variables in body
IF OBJECT_ID('dbo.fn_scalar_with_vars') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_with_vars;
GO
CREATE FUNCTION dbo.fn_scalar_with_vars (@date DATETIME)
RETURNS INT
AS BEGIN
    DECLARE @DateKey INT;
    SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @date, 112));
    RETURN @DateKey;
END;
GO
SELECT dbo.fn_scalar_with_vars(GetDate());
GO

SECOND:
Scalar-value UDF, which does not use intermediate calculations, and almost immediately returns the result of the calculation.
The body of function is placed into BEGIN-END container.

-- Recreate scalar-value UDF with direct expression (without variables) in body
IF OBJECT_ID('dbo.fn_scalar_direct_expression') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_direct_expression;
GO
CREATE FUNCTION dbo.fn_scalar_direct_expression (@date DATETIME)
RETURNS INT
AS BEGIN
    RETURN CONVERT(INT, CONVERT(VARCHAR(8), @date, 112));
END;
GO
SELECT DateKey = dbo.fn_scalar_direct_expression(GetDate());
GO

Scalar-value UDF which returns not scalar value, but a table-result.
For data storage uses UDF a table variable.
The body of function is placed into BEGIN-END container.

-- Recreate table-value UDF which returns table-variable
IF OBJECT_ID('dbo.fn_non_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_non_inline_table;
GO
CREATE FUNCTION dbo.fn_non_inline_table (@date DATETIME)
RETURNS @DateKey TABLE(DateKey INT)
AS BEGIN
    INSERT INTO @DateKey
    VALUES (CONVERT(INT, CONVERT(VARCHAR(8), @date, 112)));
RETURN;
END
GO
SELECT DateKey FROM dbo.fn_non_inline_table(GetDate());
GO

Finally the UDF, which returns a non-scalar value, but a table.
There is no any BEGIN-END container, and the expression is placed immediately after the AS expression.
Such UDFs are called "Inline Table-Value UDF".

-- Recreate inlide table-value UDF
IF OBJECT_ID('dbo.fn_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_inline_table;
GO
CREATE FUNCTION dbo.fn_inline_table (@date DATETIME)
RETURNS TABLE
AS
    RETURN SELECT DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @date, 112));
GO
SELECT DateKey FROM dbo.fn_inline_table(GetDate());
GO

---

Let us test the direct evaluation of the expression in the query, and calls of all four UDFs.
Tests were performed on a virtual machine running on my NoteBook-e.
On other computers the results may differ.

The test script is defined so:
- in the first step we will store the current date and time in the variable
- for each row of the test table we will performed a calculation
- the test request substitute this text: / * Put calculation there * /.
We will count the number of milliseconds between start and time.

-- Script to test calculations
DECLARE
    @DateKey INT,
    @StartedAt DATETIME2 = GETDATE(); 

/* Put calculation there */

PRINT 'Elapsed time (Direct calculation): '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

Calculation directly in query:
300 ms.

SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), Datum, 112))
FROM dbo.TestUDFs;

Scalar-value UDF with variables and body:
1570 ms.

SELECT @DateKey = dbo.fn_scalar_with_vars(Datum)
FROM dbo.TestUDFs;

Scalar-value UDF with direct expression and body:
1274 ms.

SELECT @DateKey = dbo.fn_scalar_direct_expression(Datum)
FROM dbo.TestUDFs;

Non-inline table-value UDF with table-variable and body:
29623 ms.

SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_non_inline_table(Datum);

And now surprise - Inline table-value UDF:
210 ms!!! - at least the same result as in the first case, where a computation request occurs immediately.

SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_inline_table(Datum);

---

All-in-one Script (an execution takes about one minute):

-- SET NOCOUNT ON to prevent extra log messages:
-- See: https://msdn.microsoft.com/en-us/library/ms189837%28v=sql.110%29.aspx
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TestUDFs') IS NOT NULL
    DROP TABLE dbo.TestUDFs;
CREATE TABLE dbo.TestUDFs(
    [Date] Date
);
-- Fill test-table
DECLARE
    @Date Date = GetDate(),
       @i INT  = 0;
WHILE @i < 1000 * 365 BEGIN
    SET @Date = DATEADD(day, 1, @Date);
    INSERT INTO dbo.TestUDFs VALUES (@Date);
    SET @i = @i + 1;
END;
-- Check if table is filled
DECLARE @CNT INT = (SELECT COUNT(*) FROM dbo.TestUDFs);
PRINT 'Rows in TestUDFs table: ' + CAST(@CNT AS VARCHAR(20));
GO
IF OBJECT_ID('dbo.fn_scalar_with_vars') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_with_vars;
GO
CREATE FUNCTION dbo.fn_scalar_with_vars (@Date DATETIME)
RETURNS INT
AS BEGIN
    DECLARE @DateKey INT;
    SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
    RETURN @DateKey;
END;
GO
IF OBJECT_ID('dbo.fn_scalar_direct_expression') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_direct_expression;
GO
CREATE FUNCTION dbo.fn_scalar_direct_expression (@Date DATETIME)
RETURNS INT
AS BEGIN
    RETURN CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
END;
GO
IF OBJECT_ID('dbo.fn_non_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_non_inline_table;
GO
CREATE FUNCTION dbo.fn_non_inline_table (@Date DATETIME)
RETURNS @DateKey TABLE(DateKey INT)
AS BEGIN
    INSERT INTO @DateKey
    VALUES (CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112)));
RETURN;
END
GO
IF OBJECT_ID('dbo.fn_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_inline_table;
GO
-- Recreate scalar-value UDF with direct expression (without variables) in body
IF OBJECT_ID('dbo.fn_scalar_direct_expression') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_direct_expression;
GO
CREATE FUNCTION dbo.fn_scalar_direct_expression (@Date DATETIME)
RETURNS INT
AS BEGIN
    RETURN CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
END;
GO
-- Recreate table-value UDF which returns table-variable
IF OBJECT_ID('dbo.fn_non_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_non_inline_table;
GO
CREATE FUNCTION dbo.fn_non_inline_table (@Date DATETIME)
RETURNS @DateKey TABLE(DateKey INT)
AS BEGIN
    INSERT INTO @DateKey
    VALUES (CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112)));
RETURN;
END
GO
-- Recreate inlide table-value UDF
IF OBJECT_ID('dbo.fn_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_inline_table;
GO
CREATE FUNCTION dbo.fn_inline_table (@Date DATETIME)
RETURNS TABLE
AS
    RETURN SELECT DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
GO
/********** Test all methods **********/
DECLARE
    @DateKey INT,
    @StartedAt DATETIME2 = GETDATE();

PRINT '
Calculation directly in query:'
SET @StartedAt = GETDATE();
SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), [Date], 112))
FROM dbo.TestUDFs;
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

PRINT '
Scalar-value UDF with variables and body:'
SET @StartedAt = GETDATE();
SELECT @DateKey = dbo.fn_scalar_with_vars([Date])
FROM dbo.TestUDFs;
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

PRINT '
Scalar-value UDF with direct expression and body:'
SET @StartedAt = GETDATE();
SELECT @DateKey = dbo.fn_scalar_direct_expression([Date])
FROM dbo.TestUDFs;
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

PRINT '
Non-inline table-value UDF with table-variable and body:'
SET @StartedAt = GETDATE();
SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_non_inline_table([Date]);
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

PRINT '
Inline table-value UDF:'
SET @StartedAt = GETDATE();
SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_inline_table([Date]);
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

GO
-- Restore SET NOCOUNT OFF
SET NOCOUNT OFF;

An execution of this Script generates this result message:

Rows in TestUDFs table: 365000

Calculation directly in query:
    Elapsed time: 300 ms

Scalar-value UDF with variables and body:
    Elapsed time: 1570 ms

Scalar-value UDF with direct expression and body:
    Elapsed time: 1274 ms

Non-inline table-value UDF with table-variable and body:
    Elapsed time: 29623 ms

Inline table-value UDF:
    Elapsed time: 210 ms

MSSQL - Function to format detailed error message

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

GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to format last error as detailed message

Usage: Log last error
    BEGIN TRY
        DECLARE @i INT;
        PRINT 'Try division by zero'
        SET @i = 1/0;
        PRINT 'After'
    END TRY
    BEGIN CATCH
        -- Display error
        PRINT dbo.fnGetDetailedErrorMessage();
        IF @@TRANCOUNT > 0 ROLLBACK;
    END CATCH;
=============================================*/
CREATE FUNCTION dbo.fnGetDetailedErrorMessage()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN ('ERROR:
        ErrorNumber        = ' + CAST(ERROR_NUMBER() AS VARCHAR(20)) + ',
        ErrorSeverity    = ' + CAST(ERROR_SEVERITY() AS VARCHAR(20)) + ',
        ErrorState        = ' + CAST(ERROR_STATE() AS VARCHAR(20)) + ',
        ErrorProcedure    = "' + ISNULL(ERROR_PROCEDURE(), '') + ',
        ErrorLine        = ' + CAST(ERROR_LINE() AS VARCHAR(20)) + ',
        ErrorMessage    = "' + ERROR_MESSAGE() + '"' + CHAR(13) + CHAR(10));
END;
GO

BEGIN TRY
    DECLARE @i INT;
    PRINT 'Try division by zero'
    SET @i = 1/0;
    PRINT 'After'
END TRY
BEGIN CATCH
    -- Display error
    PRINT dbo.fnGetDetailedErrorMessage();
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH;

Results of execution:

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: