Archive

Posts Tagged ‘T-SQL’

TRANSLATE()–Replace multiple characters in single function call

October 6, 2017 Leave a comment

TRANSLATE() is a new SQL Server 2017 string function which supports replacing multiple characters in one function call, This will help us in avoid using multiple replace statements

Syntax

TRANSLATE(InputString, StringPattern, StringReplacement)

Example Usage

In this example, we have values delimited by , for field terminator and ~ for row terminator, which we are going to replace , (comma) with | (pipe) and ~ (Tilde) with # (Hash)

DECLARE @strInput VARCHAR(255) = ‘Test1,Test2,Test3,Test4,Test5~Test11,Test12,Test13,Test14,Test15~Test21,Test22,Test23,Test24,Test25’

SELECT @strInput [Input],
REPLACE(REPLACE(@strInput, ‘,’, ‘|’), ‘~’, ‘#’) [SQL 2016 & Before – Output],
TRANSLATE(@strInput, ‘,~’, ‘|#’) [SQL 2017 Output]

Output

capture20171006114812618

As you can see, in SQL Server 2016 and prior, we need to use 2 REPLACE() function calls, in SQL Server 2017, we can use 1 TRANSLATE() function call to implement the same functionality

Hope you find this post useful !!!

Advertisements

STRING_AGG() – Concatenate values in Rows with Separator

October 5, 2017 Leave a comment

SQL Server 2017 introduces a very helpful String function to concatenate values from rows as one value with separator and main advantage is it doesn’t add the separator in the end of the value

Syntax for the function is as follows

STRING_AGG ( expression, separator ) [
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) ]

Simple Example Usage – Building CSV from one field

SELECT String_Agg(strData, ‘,’) AS CSV FROM dbo.Test

capture20171005142123779

Example Usage with ORDER BY option on different field

SELECT String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV FROM dbo.Test

capture20171005142257083

Example Usage with GROUP BY and ORDER BY

SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV1
FROM dbo.Test
GROUP BY Category

capture20171005142405536

What is not supported ?

ORDER BY is supported only for one field or one set of fields and one direction only, , see below example, We cannot use ORDER BY intPK DESC for first STRING_AGG() and ORDER BY intPK ASC for next STRING_AGG

SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV1,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK ASC) AS CSV2
FROM dbo.Test
GROUP BY Category

 

The above query will return the below error message

Msg 8711, Level 16, State 1, Line 15
Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.

Alternative way to get around this is use multiple queries and join them.

;WITH cteA
AS
(
SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV1
FROM dbo.Test
GROUP BY Category
),
cteB
AS
(
SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK ASC) AS CSV2
FROM dbo.Test
GROUP BY Category
)
SELECT
A.Category, A.CSV1, B.CSV2
FROM cteA A
LEFT JOIN cteB B ON A.Category = B.Category

capture20171005143658060

Hope you find this post helpful !!!

TRIM()–New String function in SQL Server 2017

October 4, 2017 Leave a comment

SQL Server 2017 introduces long awaited string function which supports both LTRIM() and RTRIM() together

Syntax for the function is as follows

TRIM ( [ characters FROM ] string )

Example usage

SELECT TRIM(‘   SQLXpertise   ‘) [SQL2017]

Output

image

In addition to trimming spaces, TRIM() function supports removing other characters in prefix and suffix as well, see below example, we are providing #, ~, <SPACE> as characters to removed from Prefix and Suffix

Example with Character removal

SELECT TRIM(‘#~ ‘ FROM ‘#   SQLXpertise   ~’) [SQL2017]

 

Output

image

This new function will be helpful in using one TRIM() call rather than 3 function calls of LTRIM(), RTRIM() and REPLACE()

Hope  you find this post helpful !!!

CONCAT_WS() – Concatenate with Separator–New String Function in SQL Server 2017

October 3, 2017 Leave a comment

SQL Server 2017 has a new String function which supports concatenating multiple values with separator, ex. Comma (,) or Pipe (|)

This function will come handy, if you want to generate a CSV file quickly

Syntax for the function is as follows

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

Example Usage

CREATE TABLE [dbo].[Test](
[intPK] [int] IDENTITY(1,1) NOT NULL,
[strData] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[intPK] ASC
)
)
GO

INSERT INTO dbo.test (strData) VALUES (‘Test1’)
INSERT INTO dbo.test (strData) VALUES (‘Test2’)
INSERT INTO dbo.test (strData) VALUES (‘Test3’)
INSERT INTO dbo.test (strData) VALUES (‘Test4’)
INSERT INTO dbo.test (strData) VALUES (‘Test5’)

SELECT CONCAT_WS(‘|’, intPK, strData) AS Result FROM dbo.Test

In above example we are concatenating 2 fields using pipe as separator

Output

capture20171003120119644

NULL Handling

If any of the data fields has NULL, then we need to use ISNULL() or COALESCE() to handle this, else, the structure of the output will change

ALTER TABLE dbo.Test ADD strNullData VARCHAR(50) NULL

UPDATE dbo.Test SET strNullData=’A’ WHERE intPK=1
UPDATE dbo.Test SET strNullData=’B’ WHERE intPK=3
UPDATE dbo.Test SET strNullData=’C’ WHERE intPK=5

SELECT CONCAT_WS(‘|’, intPK, strNullData, strData) Result FROM dbo.Test

In the below output, rows 1,3 and 5 has 3 fields and rows 2 and 4 has only 2 fields

capture20171003125644345

Query with ISNULL() Handling

SELECT CONCAT_WS(‘|’, intPK, ISNULL(strNullData, ”), strData) Result FROM dbo.Test

Now the below output will have consistent list of fields

capture20171003125845846

Hope you find this helpful !!!

Catch ‘n Throw Errors in SQL Server “Denali”

May 6, 2011 Leave a comment

SQL Server “Denali” adds a new feature in Error Handling called “THROW”

Current version already has RAISERROR, which you can already use to raise Error from your procedures, then why do we need this new “THROW” option,

Let us see the differences

/* RAISERROR */

BEGIN TRY 
    SELECT 1/0 
END TRY 
BEGIN CATCH 
    
    DECLARE @ERRORNUMBER INT = ERROR_NUMBER() 
    DECLARE @ERRORMESSAGE VARCHAR(500) = ERROR_MESSAGE() 
    
    SELECT @ERRORNUMBER, @ERRORMESSAGE

    RAISERROR(@ERRORNUMBER, 16, -1) 
END CATCH

In the above code, we are creating Divide by Zero error in the Try Block and then Catching that Error in Catch Block and Display the Error details and then need to return the same error to calling application

Output from SELECT:

image

Msg 2732, Level 16, State 1, Line 11 Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.

RAISERROR(@ERRORNUMBER, 16, –1) causes the above error instead of returning that error to application

See the same implementation using THROW and how it works

/* THROW Demo */

BEGIN TRY 
    SELECT 1/0 
END TRY 
BEGIN CATCH 
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);

    THROW;
END CATCH 
(0 row(s) affected)
Error Number: 8134
Error Line: 5
Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.

 

The Error number and Error Line was printed and then the same error was thrown back to calling application

You can use THROW to log errors in CATCH block and return the same error back to calling application easily.

Server Side Paging simplified in SQL Server “Denali”

May 5, 2011 Leave a comment

SQL Server “Denali” is the new upcoming version of SQL Server, it has numerous new features

Let us review the new built-in Paging feature “FETCH FIRST”, “FETCH NEXT”

–How to Fetch 1st Page of 10 Rows

USE [AdventureWorks2008R2]
GO

DECLARE @PageNumber INT = 1
DECLARE @PageSize INT = 10

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
  FETCH FIRST @PageSize ROWS ONLY

–How to Fetch 2nd Page of 10 Rows

SET @PageNumber = 2

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

— How to Fetch all records skipping first 100 Records

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET 100 ROWs

— How to implement Customizable Paging support using the new feature

–Create a Personalization table to store PageSettings

CREATE TABLE PageSettings
(
    LoggedInUserName    VARCHAR(128),
    PageSize        Int
)

–Add some Page setting Records

INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘bill’,10)
INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘gates’,20)

— Create a Sample Stored Procedure to implement customized paging

CREATE PROCEDURE sp_FetchProducts
(
    @LoginName VARCHAR(128)
)
As
BEGIN

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET 10 ROWS
  FETCH NEXT (SELECT PageSize From PageSettings WHERE LoggedInUserName=@LoginName) ROWS ONLY 

END

–Execute the Stored Procedure under login “Bill”

EXEC sp_FetchProducts @LoginName = ‘bill’

Output: Stored Procedure returns 10 Records

image

–Execute the Stored Procedure under login “Gates”

EXEC sp_FetchProducts @LoginName = ‘gates’

Output: Stored Procedure now returns 20 Records

image

You can even customize this solution further Table wise as well.

I hope all the SQL developers will enjoy this feature, since it reduces lot of coding for implementing paging using Table Variables or Temp Table or CTEs.

%d bloggers like this: