NEW T-SQL FEATURES IN SQL SERVER 2016- VI [ STRING_ESCAPE() ]

09 Tuesday Jan 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016- VI [ STRING_ESCAPE() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

New feature – VI | [ STRING_ESCAPE() ]

Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function. In my next post I shall explain what is deterministic function.

Notes:-

1. It returns nvarchar(max) text with escaped special and control characters.
2. It currently only escape JSON special characters ( Refer below table ).

Special character	Encoded sequence
Quotation mark (“)	\”
Reverse solidus (\)	\\
Solidus (/)	\/
Backspace	\b
Form feed	\f
New line	\n
Carriage return	\r
Horizontal tab	\t

STRING_ESCAPE example

--

SELECT STRING_ESCAPE('\Pawan', 'json') AS NewTextWithAddedEscapeCharacters; 

--

OUTPUT

--

NewTextWithAddedEscapeCharacters
---------------------------------------
\\Pawan

(1 row affected)

--

Example with JSON Formatted Object

--

DECLARE @json AS VARCHAR(MAX)=''
SET @json = FORMATMESSAGE('{ "id": %d,"name": "%s", "surname": "%s" }', 1, STRING_ESCAPE('Pawan','json'), STRING_ESCAPE('Khowal','json') );  

SELECT @json

--

OUTPUT

--

---------------------------------------------------
{ "id": 1,"name": "Pawan", "surname": "Khowal" }

(1 row affected)

--

Refer below for more details-

1.https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Ids with only value “One”

09 Tuesday Jan 2018

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 1 Comment

Tags

SQL Puzzle | Ids with only value “One”

In this puzzle you need to fetch rows for the Ids where the value in the vals column is only “One”. For more details please check the sample input and expected output.

Sample Input

Id	Nums	Vals
A	141	One
A	141	Two
A	234	Two
B	981	One
C	121	One
C	111	One

Expected Output

Id	Nums	Vals
B	981	One
C	121	One
C	111	One

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE TestMultipleVals
(
	  Id VARCHAR(1)
	, Nums INT
	, Vals VARCHAR(100)
)
GO

INSERT INTO TestMultipleVals
SELECT 'A',141,'One' UNION ALL
SELECT 'A',141,'Two' UNION ALL
SELECT 'A',234,'Two' UNION ALL
SELECT 'B',981,'One' UNION ALL
SELECT 'C',121,'One' UNION ALL
SELECT 'C',111,'One'
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT Id,Nums,Vals
FROM TestMultipleVals t
WHERE t.Vals = 'One' 
AND NOT EXISTS  
(
	SELECT NULL
	FROM TestMultipleVals t2
	WHERE t2.id = t.id and t2.Vals <> t.Vals
);

--

Output-1

--                               
  
Id   Nums        Vals
---- ----------- ---------------
B    981         One
C    121         One
C    111         One

(3 rows affected)                                                 
                                    
--

Solution – 2

--

SELECT Id,Nums,Vals
FROM 
(
	SELECT * , SUM( CASE WHEN t.Vals = 'One' THEN 1 ELSE 0 END ) OVER(PARTITION BY Id) a 
		     , SUM( CASE WHEN t.Vals <> 'One' THEN 1 ELSE 0 END ) OVER(PARTITION BY Id) b
	FROM TestMultipleVals t
)k WHERE a >= b

--

Output-2

--                               
  
Id   Nums        Vals
---- ----------- ---------------
B    981         One
C    121         One
C    111         One

(3 rows affected)
                                 
--

Solution – 3

--

SELECT t.* FROM 
(
	SELECT Id 
	FROM TestMultipleVals t
	GROUP BY Id
	HAVING SUM(CASE WHEN t.Vals <> 'One' THEN 1 ELSE 0 END)=0
	AND SUM(CASE WHEN t.Vals = 'One' THEN 1 ELSE 0 END) >= 1
)k INNER JOIN TestMultipleVals t ON t.Id = k.Id

--

Output-3

--                               
  
Id   Nums        Vals
---- ----------- ---------------
B    981         One
C    121         One
C    111         One

(3 rows affected)                                                 
                                    
--

Solution – 4

--

SELECT *
FROM TestMultipleVals k
WHERE EXISTS 
(
	SELECT NULL 
	FROM TestMultipleVals t
	WHERE t.Id = k.Id
	HAVING SUM(CASE WHEN t.Vals <> 'One' THEN 1 ELSE 0 END)=0
	AND SUM(CASE WHEN t.Vals = 'One' THEN 1 ELSE 0 END) >= 1
)

--

Output-4

--                               
  
Id   Nums        Vals
---- ----------- ---------------
B    981         One
C    121         One
C    111         One

(3 rows affected)                                                
                                    
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2016- V [ DATEDIFF_BIG() ]

09 Tuesday Jan 2018

Posted by Pawan Kumar Khowal in SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions, T SQL Puzzles, Tricky SQL Queries

≈ 1 Comment

Tags

NEW T-SQL FEATURES IN SQL SERVER 2016- V [ DATEDIFF_BIG() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

New feature – V | [ DATEDIFF_BIG() ]

Returns the count (signed big integer) of the specified datepart boundaries crossed between the specified startdate and enddate. DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Notes:-

1. If the return value is out of range for bigint it returns the error.
2. Range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
3. The returns value is NULL if any of the date input is NULL.
4. This function can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.
5. Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function is deterministic.

DATEPART can be –:-

YEAR
QUARTER
MONTH
DAYOFYEAR
DAY
WEEK
HOUR
MINUTE
SECOND
MILLISECOND
MICROSECOND
NANOSECOND

Sample example for [ DATEDIFF_BIG() ]

--

SELECT DATEDIFF_BIG(YEAR, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
1

(1 row affected)

--

--

SELECT DATEDIFF_BIG(MONTH, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
12

(1 row affected)


--

--

SELECT DATEDIFF_BIG(SECOND, DATEADD(YEAR,-1,GETDATE()), GETDATE());


--

--

--------------------
31536000

(1 row affected)


--

--

SELECT DATEDIFF_BIG(MILLISECOND, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
31536000000

(1 row affected)


--

--

SELECT DATEDIFF_BIG(NANOSECOND, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
31536000000000000

(1 row affected)

--

Refer below for more details-

1.https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Improving my SQL BI Skills

Daily Archives: January 9, 2018

NEW T-SQL FEATURES IN SQL SERVER 2016- VI [ STRING_ESCAPE() ]

SQL Puzzle | Ids with only value “One”

NEW T-SQL FEATURES IN SQL SERVER 2016- V [ DATEDIFF_BIG() ]

Share this

Share this

Share this