Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Handling Special characters with For XML PATH(”) Puzzle

In this puzzle you have to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator.

Notes -If you use FOR XML PATH(”) some entries having & shows [& amp ;] This is not what we require. To handle this we need to use the cool trick – TYPE directive. Example FOR XML PATH(”),TYPE).value(‘.’,’NVARCHAR(MAX)’). So after this you do not have to worry about the HTML characters any more.

Sample Example of XML PATH(”),TYPE).value(‘.’,’NVARCHAR(MAX)’)

In this example I am just printing the prime numbers below 1000 and using & as my separator.

--                            

SET QUOTED_IDENTIFIER ON;
DECLARE @Limit AS INT = 1000
DECLARE @ AS VARCHAR(MAX) = ''
;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
),
CTE1 AS 
(
	SELECT DISTINCT Number
	FROM 
		Series num
	WHERE 
		Number >= 2 AND Number <= 1000 AND 
			NOT EXISTS 
	( 
			SELECT 1 FROM Series AS num1 WHERE num1.Number > 1
		AND  num1.Number < num.Number
		AND  num.Number % num1.Number = 0
	)	
)
,CTE2 AS
(
	SELECT
	(SELECT '&'+CAST(t2.Number AS VARCHAR(10))
	from CTE1 t2
	ORDER BY Number
	FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') a
)
SELECT @ = SUBSTRING(a,2,LEN(a)) FROM CTE2
PRINT @

--

OUTPUT

--                            

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&179&181&191&193&197&199&211&223&227&229&233&239&241&251&257&263&269&271&277&281&283&293
&307&311&313&317&331&337&347&349&353&359&367&373&379&383&389&397&401&409&419&421&431&433&439&443&449&457&461
&463&467&479&487&491&499&503&509&521&523&541&547&557&563&569&571&577&587&593&599&601&607&613&617&619&631&641
&643&647&653&659&661&673&677&683&691&701&709&719&727&733&739&743&751&757&761&769&773&787&797&809&811&821&823
&827&829&839&853&857&859&863&877&881&883&887&907&911&919&929&937&941&947&953&967&971&977&983&991&997

--

Read Related Puzzles –

https://msbiskills.com/2016/06/23/sql-puzzle-biggest-prime-number-divisor-of-an-input-number/
https://msbiskills.com/2016/06/15/sql-puzzle-the-prime-number-puzzle-v/
https://msbiskills.com/2016/06/01/sql-puzzle-5-prime-numbers-above-a-given-number/
https://msbiskills.com/2016/05/22/sql-puzzle-the-circular-prime-numbers-puzzle/
https://msbiskills.com/2015/10/30/sql-puzzle-check-prime-number-puzzle/
https://msbiskills.com/2015/10/25/sql-puzzle-the-prime-numbers-puzzle/

Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com