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 -- ```

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 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/