Feeds:
Posts
Comments

Archive for December, 2022

SQL Server 2022 – Fix – Error – Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’

Posted in Errors, SQL SERVER, tagged , , , , on December 2, 2022| Leave a Comment »

STRING_SPLIT() is one of the most used string functions, which splits the string based on one character separator. Earlier I have written an article about it in detail.

Recently, I was using STRING_SPLIT() function and I came across given below error and the reason is I used two characters separator which is NOT valid for STRING_SPLIT() function:

Error :

Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

DECLARE @String AS VARCHAR(50);
SET     @String  = '1:30PM2:30PM3:30PM4:30PM5:30PM6:30PM7:30PM';

SELECT * FROM STRING_SPLIT(@String, 'PM');
GO
--OUTPUT

Solution:

As per SQL documentation, STRING_SPLIT() function can take only one character in the separator argument.

Now, in order to solve this issue, we are going to use REPLACE() function to replace the two characters separator “PM” to one character separator “|“. Also, make sure that you MUST replace with a separator which is NOT available in your data, I used “|” which is not available in my entire data. After that, we can simply split the string using STRING_SPLIT() function as shown in the below example.

--Declare a variable
DECLARE @String AS VARCHAR(50);
 
--Set variable value 
SET @String  = '1:30PM2:30PM3:30PM4:30PM5:30PM6:30PM7:30PM';
 
--Replace PM with another single character separator '|'
SET @String  = LEFT(REPLACE(@String,'PM','|'),LEN(REPLACE(@String,'PM','|'))-1);
 
--Apply String_Split function
SELECT * FROM STRING_SPLIT(@String, '|');
GO
--OUTPUT

Conclusion:

Try to use one character separator in STRING_SPLIT() function, then you will not get above error.

Read Full Post »

SQL Server – STRING_AGG() Function

Posted in Functions, SQL SERVER, tagged , , , , on December 1, 2022| Leave a Comment »

Creating a comma separated list from a table or converting multiple rows into single row along with delimiter was never been easier in earlier (before SQL Server 2017) version of SQL Server. I have written a detailed article back in 2012 regarding this issue, where I used CURSOR, COALESCE() function, STUFF() function & XML format etc. to achieve it.

In SQL Server 2017, a new function shipped namely STRING_AGG() which can help you to achieve above mentioned functionality by using this function ONLY. By the way, instead of comma ( , ) you can use semi-colon ( ; ) or any other delimiters, it works like a charm with all delimiters.

Let me demonstrate STRING_AGG() functionality by giving some examples. I am going to use Customers table in Northwind database as shown below, which can be downloaded from here.

Sample:

As you can see in below sample table that there are many companies in each country.

USE Northwind
GO

SELECT [Country]
     , [CompanyName]
FROM   [dbo].[Customers]
ORDER BY [Country];
GO
--OUTPUT

Example 1:

In the below example, I created a company list separated by comma (delimiter) for each country. You can use any delimiter based on your requirement.

USE Northwind
GO

SELECT [Country]
     , STRING_AGG(CompanyName,', ') AS [CompanyName]
FROM   [dbo].[Customers]
GROUP BY [Country]
ORDER BY [Country];
GO
--OUTPUT

Example 2:

In the above example 1, I got comma-separated list of companies for each country but the issue is those company names do NOT display in an alphabetical sort order. To resolve it we are going to use WITHIN GROUP ORDER BY clause within function as shown below:

USE Northwind
GO

SELECT [Country]
       ,STRING_AGG(CompanyName,', ') WITHIN GROUP (ORDER BY CompanyName) AS [CompanyName]
FROM   [dbo].[Customers]
GROUP BY [Country]
ORDER BY [Country];
GO
--OUTPUT

Example 3:

In this example, I want to get list of comma separated OrderID(s) for each Company but the data does NOT exists in one table, so I will join two tables (Customers, Orders) to be able to  retrieve the data as shown below.

USE Northwind
GO

SELECT [CompanyName] 
	  , STRING_AGG(OrderID,',') AS OrderIDs
FROM   [Customers]
INNER JOIN [Orders] ON [Customers].[CustomerID]=[Orders].[CustomerID] 
GROUP BY [CompanyName];
GO
--OUTPUT

Conclusion:

I use STRING_AGG() function in my day to day SQL scripting quite frequently, I found it very handy. Do let me know if you use this function in your development and how did you find it?

Read Full Post »

« Newer Posts