SQL Server String Puzzles & Tricks

21 Tuesday Jun 2016

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

Tags

SQL Server String Puzzles

Here I come with cunning SQL Server String Puzzles / Queries which are frequently asked in SQL / Database / MSBI / SQL BI interviews from developer to Architect level. All the questions below seems easy but it would be complex situation when you face these query in Interview time.

If you have any puzzle in mind or if you want me to solve some puzzle or if you have difficulty in solving business problem please email me @ pawankkmr@gmail.com. I would be happy to help.

This would be best for you to go through following SQL SERVER String Interview questions / puzzles before attend any technical interview. Please visit Complete list of SQL SERVER Puzzles @ below- SQL SERVER Puzzles

Sr No	SQL Server String Puzzle
1	SQL Puzzle \| The String Puzzle – Combine data by one column and then another one using comma
2	SQL Puzzle \| [ Count Spaces in a string Puzzle ]
3	SQL Puzzle \| Convert a string into a table n number of times ( n = length of the string )
4	SQL Puzzle \| Convert each character of a string into a table without any delimiter
5	SQL Puzzle \| Split String Puzzle – II (Linked In)
6	SQL Puzzle \| [ Replace 6 or more Consecutive Digits with x from a string Puzzle ]
7	SQL Puzzle \| [ Multiple Ways to Split a String Puzzle ]
8	SQL Puzzle \| [ Count Spaces in a string Puzzle ]
9	SQL Puzzle \| Get Last 4 Characters from a String without Left & Right Function
10	SQL Puzzle \| Count different type of characters from a string Puzzle
11	SQL Puzzle \| Find Strings that contains % Character
12	SQL Puzzle \| The ASCII Puzzle – II
13	SQL Puzzle \| REVERSE a String Without Using Reverse Function / Recursive CTE / While Loop

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The String Puzzle – Combine data by one column and then another one using comma

21 Tuesday Jun 2016

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

≈ 3 Comments

Tags

SQL Puzzle | The String Puzzle – combine data by one column and then another one using comma

Here you have given a table with three columns – Col1, Col2, Col3. We have to group data based on Col1. Based on Col1 group combine col2 values using a comma and then combine col3 values using a comma

Please check out the sample input and the expected output below-

Sample Input

Col1	Col2	Col3
A	1	2
A	3	4
B	5	6

Expected Output

Col1	Cols
A	1, 3, 2, 4
B	5, 6

Script | use below script to create table & insert some sample data


--

CREATE TABLE One
(
	 Col1 VARCHAR(1)
	,Col2 VARCHAR(1)
	,Col3 VARCHAR(1)
)
GO

INSERT INTO One VALUES
('A', 1, 2),
('A', 3, 4),
('B', 5, 6)
GO



--

Solution 1 | XML & Stuff


--


SELECT Col1 , STUFF 
				(
					(
						SELECT ', ' + a
						FROM (	
								SELECT Col1,Col2 a,1 Id
								FROM One
								UNION ALL
								SELECT Col1,Col3 a,2 Id
								FROM One
							 )a
						WHERE a.Col1 = r.Col1 
						ORDER BY a.Id				
					FOR XML PATH('')
					) 
				,1,2,''
				) Cols
FROM One r
GROUP BY Col1


--

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 | Counting occurrences of a substring within a string

17 Friday Jun 2016

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL SERVER, T SQL Puzzles, Tricky SQL Queries

≈ 6 Comments

Tags

SQL Puzzle | Counting occurrences of a substring within a string

In this puzzle we have to count the occurrences of a substring within a string. Please check out the sample input and the expected output below-

Sample Input

DECLARE @ AS VARCHAR(1000) = ‘What the 789hell is 78 going on here dshf 78′
DECLARE @SubString VARCHAR(200) = ’78’

Expected Output

Counts
3

Solution 1 | Numbers Table


--

DECLARE @InputString VARCHAR(1000) =  'What the 789hell is 78 going on here dshf 78'
DECLARE @SubString VARCHAR(200) = '78'

SELECT COUNT(DISTINCT Number) Counts
FROM MASTER..SPT_VALUES N
WHERE SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString
GO

--

Solution 2 | String functions


--

DECLARE @ AS VARCHAR(1000) = 'What the 789hell is 78 going on here dshf 78'
DECLARE @SubString VARCHAR(200) = '78'

SELECT DATALENGTH(@) - DATALENGTH(REPLACE(@,'78','|')) Counts
GO

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Generate Numbers – Quickly

15 Wednesday Jun 2016

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

≈ Leave a comment

Tags

SQL Puzzle | Generate Numbers – Quickly

Do you know, how you can quickly generate 10000 numbers on the fly ?

Please check out the sample input and the expected output below-

Sample Input

Generate Number below 10000

Expected Output

List of number till 10000

Solution 1 – Old Trick (from Joe.Celkos.SQL.For.Smarties.Advanced.SQL.Programming)


--


;WITH SingleDigits(Number) AS 
(
	SELECT Number
	FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
	(9), (0)) AS X(Number)
)
,Series AS 
(
	SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
	from 
	SingleDigits as d1,
	SingleDigits as d2,
	SingleDigits as d3,
	SingleDigits as d4
)
SELECT * FROM Series 

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Prime Number Puzzle – V

15 Wednesday Jun 2016

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

≈ 2 Comments

Tags

SQL Puzzle | The Prime Number Puzzle – V

This is a variation in the Prime Puzzle . Here you have to return a list all prime numbers above a given number of N. And of course we cannot use any of the existing methods. 🙂

Please check out the sample input and the expected output below-

Sr No	SQL Server String Puzzle
1	SQL Puzzle \| The String Puzzle – Combine data by one column and then another one using comma
2	SQL Puzzle \| [ Count Spaces in a string Puzzle ]
3	SQL Puzzle \| Convert a string into a table n number of times ( n = length of the string )
4	SQL Puzzle \| Convert each character of a string into a table without any delimiter
5	SQL Puzzle \| Split String Puzzle – II (Linked In)
6	SQL Puzzle \| [ Replace 6 or more Consecutive Digits with x from a string Puzzle ]
7	SQL Puzzle \| [ Multiple Ways to Split a String Puzzle ]
8	SQL Puzzle \| [ Count Spaces in a string Puzzle ]
9	SQL Puzzle \| Get Last 4 Characters from a String without Left & Right Function
10	SQL Puzzle \| Count different type of characters from a string Puzzle
11	SQL Puzzle \| Find Strings that contains % Character
12	SQL Puzzle \| The ASCII Puzzle – II
13	SQL Puzzle \| REVERSE a String Without Using Reverse Function / Recursive CTE / While Loop

Sr No	SQL Server Puzzle
1	SQL Puzzle \| 5 Prime Numbers above a given number
2	SQL Puzzle \| The Circular Prime Numbers Puzzle
3	SQL Puzzle \| Check Prime Number Puzzle
4	SQL Puzzle \| The Prime Numbers Puzzle

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Improving my SQL BI Skills

Tag Archives: generate Fibonacci Series in SQL Server

SQL Server String Puzzles & Tricks

SQL Puzzle | The String Puzzle – Combine data by one column and then another one using comma

SQL Puzzle | Counting occurrences of a substring within a string

SQL Puzzle | Generate Numbers – Quickly

SQL Puzzle | The Prime Number Puzzle – V

Number
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97
101
103
107
109
113
127
131
137
139
149
151
157
163
167
173

Share this

Share this

Share this

Share this

Share this