Split alphanumeric string into numeric and alpha parts in SQL Server

Lets say we have an alphanumeric string stored in a variable in SQL Server. The fist part of the string is numeric and the second part consists of letters and/or symbols specifying a unit or a percentage:

DECLARE @str VARCHAR(MAX) = '4000 ug/ML'

We now want to split the numeric / unit parts from the variable and return them as separate fields.

If the numeric part is always at the beginning, then we can use this expression:

PATINDEX('%[0-9][^0-9]%', @str)

to get the index of the last digit. To see how this works let us have a look at the manual for PATINDEX:

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

The pattern specified catches the first occurrence of a number, [0-9], followed by a character that is not a number: [^0-9]. In other words, it returns the index of the last number.

Thus, this:

SELECT LEFT(@str, PATINDEX('%[0-9][^0-9]%', @str )) AS Number,
       LTRIM(RIGHT(@str, LEN(@str) - PATINDEX('%[0-9][^0-9]%', @str ))) As Unit

returns the following:

Number	Unit
4000	ug/ML

Click here for a live demo.

If numeric data include double values as well, like:

DECLARE @str VARCHAR(MAX) = '4000.35 ug/ML'

In this case we can use:

SELECT LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))

Using PATINDEX on the REVERSE order of the string value gets us the index of the first digit from the end of the string that follows a character that is not a number. In our example the SELECT returns 7 indicating the index of number 5 from the end of the string.

Thus, this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str)))

gives us the numeric part, and this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))) AS Numeric,
       CASE 
          WHEN CHARINDEX ('%', @str) <> 0 THEN LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX ('%', @str)))
          ELSE LTRIM(RIGHT(@str, PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))))
       END AS Unit

gives us both numeric and unit part.

Click here for a live demo.

How to fix 'Parameters were not supplied for the function' error in SQL Server

SQL Server provides a feature called Table Valued Functions. These are user-defined functions that return a table data type and can be powerful alternatives to views.

Lets try to define a simple function of this kind using the following code:

CREATE FUNCTION X
(
    -- Add the parameters for the function here
    @a nvarchar(30), 
    @b nvarchar(30)
)
RETURNS 
@X TABLE 
(
    -- Add the column definitions for the TABLE variable here
    data nvarchar(250)
)
AS
BEGIN

    INSERT INTO X (data) 
    VALUES (@a + @b)    

    RETURN 
END
GO

The function just returns a table that contains the concatenation of two strings. We now try to use the function:

SELECT *
FROM X('Hello', 'World')

We expect to get a table that contains a single record but instead we get the following error message:

Parameters were not supplied for the function 'X'.

Click here for a live demo of the error.

The problem with our code is that it declares a table variable to be returned by the function that has the same name as the function. This is syntactically correct but then, instead of doing an INSERT into the table variable, it uses the function name, which happens to be the same.

What this type of, difficult to decipher, error teaches us is that it is much cleaner to use a different name for the table variable to be returned.

CREATE FUNCTION X
(
    -- Add the parameters for the function here
    @a nvarchar(30), 
    @b nvarchar(30)
)
RETURNS 
@output TABLE 
(
    -- Add the column definitions for the TABLE variable here
    data nvarchar(250)
)
AS
BEGIN

    INSERT INTO @output (data) 
    VALUES (@a + @b)    

    RETURN 
END
GO

Click here for a live demo.

Pivot a table in SQL Server that contains yearly data

Consider the following table recording expenses:

CREATE TABLE dbo.Expenses
(
    ExpenseId INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    ExpenseType VARCHAR(20) NOT NULL,
    Amount DECIMAL(6,2) NOT NULL,
    ExpenseDate DATE NOT NULL
)   

We populate the table with the following data:

ExpenseId	ExpenseType	Amount	ExpenseDate
1	Rent	340.50	2017-01-01
2	Food	23.50	2017-01-03
3	Food	27.50	2017-04-08
4	Gasoline	50.30	2017-01-05
5	Food	27.50	2016-01-08
6	Rent	340.50	2016-01-01
7	Food	85.00	2015-07-17
8	Gasoline	48.40	2015-12-15
9	Furniture	540.00	2015-09-22
10	Rent	335.00	2015-01-01

Our aim is to get a pivoted view of this table:

ExpenseType	2015	2016	2017
Food	540.00	0.00	0.00
Furniture	85.00	27.50	51.00
Gasoline	48.40	0.00	50.30
Rent	335.00	340.50	340.50

If the list of years is static, in other words if we know before-hand the list of years to be taken into account, then we can make use of the PIVOT relational operator:

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Here, we want to turn the unique year values of column ExpenseDate:

• 2015
• 2016
• 2017

into multiple columns and perform aggregation on column Amount. This operation will be performed for each value of column ExpenseType.

To get the year values of ExpenseDate column we use SQL Server's YEAR built-in function. So, the table-valued expression that PIVOT will operate on looks like this:

SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount
FROM Expenses

PIVOT is performed for column ExpenseYear, whereas Amount column is the one to be aggregated using SUM aggregate function:

PIVOT
( 
   SUM(Amount)
   FOR ExpenseYear IN ([2015], [2016], [2017])
) AS PivotTable

Hence, the query we want looks like this:

SELECT ExpenseType, 
       COALESCE([2015], 0) AS [2015], 
       COALESCE([2016], 0) AS [2016], 
       COALESCE([2017], 0) AS [2017]
FROM (SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount
      FROM Expenses) AS SourceTable
PIVOT
( 
   SUM(Amount)
   FOR ExpenseYear IN ([2015], [2016], [2017])
) AS PivotTable

Note the usage of COALESCE around each year column: in case there are no values for a specific Expense Type - ExpenseDate pair, then 0 is produced in the output.

Click here for a live demo.