NEW T-SQL FEATURES IN SQL SERVER 2017- V [ CONCAT_WS() ]

20 Saturday Jan 2018

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

≈ 2 Comments

Tags

NEW T-SQL FEATURES IN SQL SERVER 2017- V [ CONCAT_WS() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2017 | CONCAT_WS()

New feature – X [ CONCAT_WS() ) ]

This function concatenates a variable number of arguments with a delimiter specified in the first argument. (CONCAT_WS indicates concatenate with separator.)

Notes:-

1. This function is available in following SQL Versions
SQL Server (starting with 2017)
Azure SQL Database
Azure SQL Data Warehouse
Parallel Data Warehouse

2. The syntax of the function is
CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

3. If all the arguments are null, an empty string of type varchar(1) is returned.

4. Null values are ignored during concatenation, and does not add the separator.

5. This function will return a string.

Example of CONCAT_WS()

--

SELECT CONCAT_WS( ' - ', database_id, recovery_model_desc, containment_desc,'pawan') AS DatabaseInfo
FROM sys.databases;

--

OUTPUT

--

DatabaseInfo
------------------------------------
1 - SIMPLE - NONE - pawan
2 - SIMPLE - NONE - pawan
3 - FULL - NONE - pawan
4 - SIMPLE - NONE - pawan
5 - FULL - NONE - pawan
7 - FULL - NONE - pawan

(6 rows affected)

--

Refer Microsoft LINK below for more details-

1.https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

NEW T-SQL FEATURES IN SQL SERVER 2017- IV [ TRANSLATE() ]

20 Saturday Jan 2018

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

≈ 2 Comments

Tags

NEW T-SQL FEATURES IN SQL SERVER 2017- IV [ TRANSLATE() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2017 | TRANSLATE()

New feature – X [ TRANSLATE() ]

This function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.

Notes:-

1. This function is available in following SQL Versions
SQL Server (starting with 2017)
Azure SQL Database
Azure SQL Data Warehouse
Parallel Data Warehouse

2. The syntax of the function is
TRANSLATE ( inputString, characters, translations)

3. The function returns a character expression of the same type as inputString where characters from the second argument are replaced with the matching characters from third argument.

4. The TRANSLATE function will return an error if characters and translations have different lengths.

5. The TRANSLATE function should return unchanged input if null vales are provided as characters or replacement arguments.

6. The functioning of the TRANSLATE function is equivalent to using multiple REPLACE functions.

Example of TRANSLATE()

--

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

--

OUTPUT

--

2*(3+4)/(7-2)

--

Note

The TRANSLATE function in this example is equivalent to but much simplier than the following statement using REPLACE:

--

SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')');


--

Refer Microsoft LINK below for more details-

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Get First Character After all occurrences of Underscore from the string

19 Friday Jan 2018

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

≈ Leave a comment

Tags

SQL Puzzle | Get First Character After all occurrences of Underscore from the string

In this puzzle you have to get first character after underscore from the string. The catch is that you have to fetch all the first character from all the occurrences of underscore. For more details please check the sample input and expected output.

Sample Input

Input
——————–
_a_b_c_d_

Expected Output

finalstr
abcd

Script

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

--

DECLARE @ AS VARCHAR(1000) = '_a_b_c_d_'
SELECT @

--

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 | for SQL SERVER 2012-

--

DECLARE @x AS XML=''
DECLARE @ AS VARCHAR(1000) = '_a_b_c_d_'
SET @x = CAST('<A>'+ REPLACE(@,'_','</A><A>')+ '</A>' AS XML)
;WITH CTE AS
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)      
)
,CTE1 AS 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM CTE
)
,CTE2 AS
(
	SELECT SUBSTRING(Value,1,1) v , rnk FROM CTE1
)
SELECT CASE WHEN LEFT(@,1) <> '_' THEN MAX(SUBSTRING(u,2,LEN(u))) ELSE MAX(u) END finalstr from (
SELECT  
		(
			SELECT '' + v  
			FROM CTE2 a               
			FOR XML PATH('') 
		)u
FROM CTE2 )x
               


--

Output-1

--                            

finalstr
----------------
abcd

(1 row 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

SQL Puzzle | How many times the value of a column has changed?

19 Friday Jan 2018

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

≈ 1 Comment

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, find last occurrence of character in string sql server, Free Download SQL SERVER Interview questions, Get Next Value Puzzle, How many times the value of a column has changed?, Huge blank areas in database field, Improve SQL Skills, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Remove Huge Multiple Spaces from a DB Fields, Remove Multiple Spaces from a DB Fields, Separate Int and char from a string column, Single Quote update SQL, SQL, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The Next Working Date Puzzle, The Status Puzzle, The Sum of maximum consecutive values puzzle (1), Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, update data that contain apostrophe, Update Data that with a string contains apostrophe, Update puzzle | Update Data that with a string contains apostrophe

SQL Puzzle | How many times the value of a column has changed?

In this puzzle you have to find out how many time the value of Status column has changed for each Event. For more details please check the sample input and expected output.

Sample Input

Id	EventId	RecoredTime	Status
1	1	2017-10-11 15:35:24.100	T
2	1	2017-10-21 15:35:24.100	T
3	1	2017-10-31 15:35:24.100	T
4	1	2017-11-10 15:35:24.103	F
5	1	2017-11-20 15:35:24.103	T
6	1	2017-11-30 15:35:24.103	F
7	2	2017-10-11 15:35:24.103	F
8	2	2017-10-21 15:35:24.103	F
9	2	2017-10-31 15:35:24.103	T
10	2	2017-11-10 15:35:24.103	F
11	2	2017-10-31 15:35:24.103	T
12	2	2017-11-10 15:35:24.103	F

Expected Output

EventId	NumberOfChanges
1	3
2	4

Script

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

--

create table Event (Id int,EventId int,RecoredTime datetime,Status char(1))
GO

insert into Event values (1,1,getdate()-100,'T')
insert into Event values (2,1,getdate()-90,'T')
insert into Event values (3,1,getdate()-80,'T')
insert into Event values (4,1,getdate()-70,'F')
insert into Event values (5,1,getdate()-60,'T')
insert into Event values (6,1,getdate()-50,'F')
insert into Event values (7,2,getdate()-100,'F')
insert into Event values (8,2,getdate()-90,'F')
insert into Event values (9,2,getdate()-80,'T')
insert into Event values (10,2,getdate()-70,'F')
insert into Event values (11,2,getdate()-80,'T')
insert into Event values (12,2,getdate()-70,'F') 
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 | for SQL SERVER 2012-

--

;WITH CTE1 AS
(
    SELECT 
		* , ROW_NUMBER() OVER  (PARTITION BY EventId ORDER BY Id) rnk 
    FROM [Event]
)
,CTE2 AS
(
	SELECT *,CASE WHEN [status] =
	( SELECT TOP 1 [Status] FROM CTE1 c1 WHERE c2.EventId = c1.EventId 
		AND c1.rnk > c2.rnk ORDER BY rnk
	) THEN 0 ELSE 1 END  cols
	FROM CTE1 c2  
)
,CTE3 AS
(
    SELECT * , SUM(cols) OVER (PARTITION BY EventId ORDER BY Id,rnk) - 1 grouper FROM CTE2
)
SELECT EventId , MAX(grouper) NumberOfChanges FROM CTE3
GROUP BY EventId  

--

Output-1

--                               
  
EventId     NumberOfChanges
----------- ---------------
1           3
2           4

(2 rows affected)

                                             
                                    
--

Solution – 2 | for SQL SERVER 2012+

--

;WITH CTE1 AS
(
    SELECT 
              * , ROW_NUMBER() OVER  (PARTITION BY EventId ORDER BY Id) rnk 
    FROM [Event]
)
,CTE2 AS
(
    SELECT *, 
        CASE WHEN [Status] = LAG([Status]) 
        OVER(PARTITION BY EventId ORDER BY Id) THEN 0 ELSE 1 END cols 
    FROM CTE1 c2         
)
,CTE3 AS
(
    SELECT * , SUM(cols) OVER (PARTITION BY EventId ORDER BY Id,rnk) - 1 grouper FROM CTE2
)
SELECT EventId , MAX(grouper) NumberOfChanges FROM CTE3
GROUP BY EventId  
 

--

Output-1

--                               
  
EventId     NumberOfChanges
----------- ---------------
1           3
2           4

(2 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

SQL Puzzle | Find Non matching records from Second table

10 Wednesday Jan 2018

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

≈ 3 Comments

Tags

SQL Puzzle | Find Non matching records from Second table

In this puzzle you need to find the non matching records from column Vals. Need Vals column from SecondTable and Id column from firstTable. For more details please check the sample input and expected output.

Sample Input

First Table

ID	Vals
1	a
2	b
3	c
4	NULL

Second Table

ID	Vals
1	a
2	NULL
3	z
4	NULL

Expected Output

Id	Vals
2	NULL
3	z
4	NULL

Script

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

--

CREATE TABLE firstTable
(
	 ID  INT    	                
	,Vals VARCHAR(10)     
)
GO

INSERT INTO firstTable VALUES
(1    ,'a'),
(2    ,'b'),
(3    ,'c'),
(4	  , NULL)
GO

CREATE TABLE SecondTable
(
	 ID  INT    	                
	,Vals VARCHAR(10)   
)
GO

INSERT INTO SecondTable VALUES
(1    ,'a'),
(2    ,NULL),
(3    ,'z'),
(4    ,NULL) 
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 f.Id,s.Vals FROM firstTable f
INNER JOIN SecondTable s ON f.ID = s.ID 
AND ( f.Vals <> s.Vals OR f.Vals IS NULL OR s.Vals IS NULL )

--

Output-1

--                               
  
Id          Vals
----------- ----------
2           NULL
3           z
4           NULL

(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

Improving my SQL BI Skills

Tag Archives: The Sum of maximum consecutive values puzzle (1)

NEW T-SQL FEATURES IN SQL SERVER 2017- V [ CONCAT_WS() ]

NEW T-SQL FEATURES IN SQL SERVER 2017- IV [ TRANSLATE() ]

SQL Puzzle | Get First Character After all occurrences of Underscore from the string

SQL Puzzle | How many times the value of a column has changed?

SQL Puzzle | Find Non matching records from Second table

Share this

Share this

Share this

Share this

Share this