The fun factor: Magical numbers and other peculiarities

Question

Post reply

The fun factor: Magical numbers and other peculiarities

Eirikur Eiriksson

SSC Guru

Points: 182911
More actions
June 15, 2014 at 6:06 am

#396912

Looking for something that brings a smile:-D, here's the first contribution
😎
DECLARE @QUESTION VARCHAR(50) = 'Last time England won the World Cup?';
SELECT CONVERT(BIGINT,CONVERT(VARBINARY(50),@QUESTION,0),1) ^ 0x726C642043757791
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mickyT SSChampion Points: 10360 More actions · Answer 1

To stick with a theme 🙂

DECLARE @Team1 VARCHAR(10) = 'Italy'

DECLARE @Team2 VARCHAR(10) = 'England'

SELECT @Team1 + ': ' + CAST(LEN(@Team1) % 3 AS VARCHAR(2)),

@Team2 + ': ' + CAST(LEN(@Team2) % 3 AS VARCHAR(2))

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

mickyT (6/15/2014)
To stick with a theme 🙂

:-DBrilliant!

😎

mickyT SSChampion Points: 10360 More actions · Answer 3

Without wanting to pick on any nation in particular;-)

SELECT TeamName + ': '+ CAST(Score AS VARCHAR(2))

FROM (VALUES ('England'), ('All Blacks')) team (TeamName)

CROSS APPLY (

SELECT 4 * LEN(REPLACE(REPLACE(TeamName,SUBSTRING(TeamName,1,1),''),' ','')) +

(LEN(TeamName) % 4) * (LEN(TeamName) % 2)

) result(Score)

ORDER BY TeamName

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

It's not very fancy, but I have found a query where NOLOCK really is desirable...

SELECT result FROM fifaworldcup2014 [highlight="#ffff11"]WITH(NOLOCK)[/highlight] WHERE team='ENGLAND'

Here's hoping for that "incorrect data" we are constantly warned about 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mickyT SSChampion Points: 10360 More actions · Answer 5

Promise this will be my last one:Whistling:

DECLARE @Question VARCHAR(50) = 'Year that England last beat Italy?'

SELECT CAST(

POWER(LEN(Italy),ItalyLastScore) *

POWER(LEN(England),EnglandLastScore) *

(LEN(@Question) / (ItalyLastScore + EnglandLastScore))

- (ItalyTotalWins - EnglandTotalWins)

- (TotalDraws / 2)

AS INT

)

FROM (VALUES ('Italy',2.0,'England',1.0,11.0,8.0,6.0))

Stats(Italy, ItalyLastScore,

England, EnglandLastScore,

ItalyTotalWins, EnglandTotalWins, TotalDraws)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

SELECT 'Last Time Belgium Won the World Cup' = 1/0;

🙁

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

mister.magoo (6/15/2014)
It's not very fancy, but I have found a query where NOLOCK really is desirable...
SELECT result FROM fifaworldcup2014 [highlight="#ffff11"]WITH(NOLOCK)[/highlight] WHERE team='ENGLAND'
Here's hoping for that "incorrect data" we are constantly warned about 😛

Tried the code but something is wrong, constantly got the same error message

😎

'Transaction (Process ID 2014) was lost on resources with another process

and has been chosen as the tournament victim. Rerun tournament.'

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Eirikur Eiriksson (6/16/2014)
mister.magoo (6/15/2014)
It's not very fancy, but I have found a query where NOLOCK really is desirable...
SELECT result FROM fifaworldcup2014 [highlight="#ffff11"]WITH(NOLOCK)[/highlight] WHERE team='ENGLAND'
Here's hoping for that "incorrect data" we are constantly warned about 😛
Tried the code but something is wrong, constantly got the same error message
😎
'Transaction (Process ID 2014) was lost on resources with another process
and has been chosen as the tournament victim. Rerun tournament.'

Use trace flag 1966

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

my effort...too much time on my hands 😀

DECLARE @Question VARCHAR(100) =

'The Answer to the Ultimate Question of Life, the Universe, and Everything'

DECLARE @Source VARCHAR(100) =

'The Hitchhikers Guide to the Galaxy'

DECLARE @Answer VARCHAR(10)

SET NOCOUNT ON

IF object_id('tempdb..#ParallelUniverse') IS NOT NULL

DROP TABLE #ParallelUniverse

SELECT top (len(@Source)) IDENTITY(INT, 1, 1) n

INTO #ParallelUniverse FROM master.sys.all_columns

SET @Answer = (select len(@Question) - (SELECT MAX(n) from

((SELECT n FROM #ParallelUniverse )

EXCEPT

(SELECT (PU1.n * PU2.n) AS cn

FROM #ParallelUniverse AS PU1, #ParallelUniverse AS PU2

WHERE PU1.n BETWEEN 2 AND CEILING (SQRT (1000))

AND PU2.n BETWEEN 2 AND CEILING (SQRT (1000))

AND PU1.n <= PU2.n

AND (PU1.n * PU2.n) <= 1000) )x))

PRINT @Question + ' is.. ' + @Answer

SET NOCOUNT OFF

/*code to find primes courtesy of J Celko in this discussion:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/90a58cc7-dada-476a-8d27-f744c71940e6/how-to-find-whether-is-a-prime-or-non-prime-with-one-select-statement?forum=transactsql

*/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

J Livingston SQL (6/16/2014)
my effort...too much time on my hands 😀
...

Nice 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

graham.smith SSC Enthusiast Points: 143 More actions · Answer 11

You all have too much time on your hands, Eirik, get back to work....... 😀

Graham

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 12

graham.smith (6/16/2014)
You all have too much time on your hands, Eirik, get back to work....... 😀
Graham

Don't worry my friend, this is a key to enjoying work!

I have been laughing all the way to work and back, thank you guys! You would all do good in the Office of National Statistics:-P

Please don't stop, I'll contribute when I can;-)

😎

mickyT SSChampion Points: 10360 More actions · Answer 13

J Livingston SQL (6/16/2014)
my effort...too much time on my hands 😀
DECLARE @Question VARCHAR(100) =
'The Answer to the Ultimate Question of Life, the Universe, and Everything'
DECLARE @Source VARCHAR(100) =
'The Hitchhikers Guide to the Galaxy'
DECLARE @Answer VARCHAR(10)
SET NOCOUNT ON
IF object_id('tempdb..#ParallelUniverse') IS NOT NULL
DROP TABLE #ParallelUniverse
SELECT top (len(@Source)) IDENTITY(INT, 1, 1) n
INTO #ParallelUniverse FROM master.sys.all_columns
SET @Answer = (select len(@Question) - (SELECT MAX(n) from
((SELECT n FROM #ParallelUniverse )
EXCEPT
(SELECT (PU1.n * PU2.n) AS cn
FROM #ParallelUniverse AS PU1, #ParallelUniverse AS PU2
WHERE PU1.n BETWEEN 2 AND CEILING (SQRT (1000))
AND PU2.n BETWEEN 2 AND CEILING (SQRT (1000))
AND PU1.n <= PU2.n
AND (PU1.n * PU2.n) <= 1000) )x))
PRINT @Question + ' is.. ' + @Answer
SET NOCOUNT OFF
/*code to find primes courtesy of J Celko in this discussion:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/90a58cc7-dada-476a-8d27-f744c71940e6/how-to-find-whether-is-a-prime-or-non-prime-with-one-select-statement?forum=transactsql
*/

Awesome:-)

mickyT SSChampion Points: 10360 More actions · Answer 14

One to make you smile if you have 2012:-)

DECLARE @smiley varchar(20) = 'Dont Worry Be Happy';

WITH Params AS (

SELECT MAX(CASE WHEN ItemNumber = 1 THEN Val END) OFFSET,

MAX(CASE WHEN ItemNumber = 2 THEN Val END) BASE,

MAX(CASE WHEN ItemNumber = 3 THEN Val END) BASEMULTIPLIER,

MAX(CASE WHEN ItemNumber = 4 THEN Val END) BASEDIVISOR

FROM (

SELECT ItemNumber, Item, LEN(Item) Val

FROM [dbo].[DelimitedSplit8K](@smiley,' ')

) s

)

SELECT @smiley Quote, Geometry::Point(BASE * BASEMULTIPLIER, BASE * BASEMULTIPLIER,0).STBuffer(BASE * BASEMULTIPLIER).STDifference((

SELECT Geometry::UnionAggregate(g)

FROM (

SELECT Geometry::Point(BASE * BASEMULTIPLIER - OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params

UNION ALL

SELECT Geometry::Point(BASE * BASEMULTIPLIER + OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params

UNION ALL

SELECT Geometry::STGeomFromText(

CONCAT('CIRCULARSTRING(',

BASE * BASEMULTIPLIER - OFFSET, ' ', BASE * BASEMULTIPLIER - OFFSET, ', ',

BASE * BASEMULTIPLIER , ' ', (BASE * BASEMULTIPLIER - OFFSET) - ((BASE * BASEMULTIPLIER) / (BASE * BASEMULTIPLIER - OFFSET)), ', ',

BASE * BASEMULTIPLIER + OFFSET, ' ', BASE * BASEMULTIPLIER - OFFSET, ')'),0).STBuffer(BASE / BASEDIVISOR) g FROM Params

) a

)) Smiley

FROM Params;

--Now switch to Spatial Results Tab :)

Edit: Tweaked Slightly