The 12 days of Christmas

Question

The 12 days of Christmas

Steve Jones - SSC Editor

SSC Guru

Points: 734489
More actions
December 23, 2022 at 12:00 am

#4125241

Comments posted to this topic are about the item The 12 days of Christmas
December 23, 2022 at 11:21 am

This was removed by the editor as SPAM
December 23, 2022 at 11:24 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

daytonbrown2 Mr or Mrs. 500 Points: 569 More actions · Answer 1

OK. I know this is an XMAS post and I shouldn't read too much into the code, but holy cow, those where Clauses (like what I did there) gave me the shivers. Ewww!

matthew.flower Default port Points: 1414 More actions · Answer 2

Sorry but the "correct" answer is wrong. The insert into Numbers is:

INSERT dbo.Numbers (n, word) VALUES

(1, 'A '),

Note the white space after A, so the first word is "A ". The suggested join ON:

SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) = n.word

Will fail for the first row because SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) for the row where gift starts with "A partridge..." will return "A" which will not equate to "A ". I am not aware of any collation options that allow a two-character string to equal a one-character string.

While it is not possible to define a function index to aid performance, the join that will work for this low data volume is simply

td.gift LIKE n.word + '%'

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

Sorry... Had an possible error in my post and took it down until I'm sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

matthew.flower wrote:

Sorry but the "correct" answer is wrong. The insert into Numbers is:
SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) = n.word
Will fail for the first row because SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) for the row where gift starts with "A partridge..." will return "A" which will not equate to "A ". I am not aware of any collation options that allow a two-character string to equal a one-character string.
While it is not possible to define a function index to aid performance, the join that will work for this low data volume is simply
td.gift LIKE n.word + '%'

I tried :

select SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) + 'x'
from TwelveDays td

It returned:

So did return 'A '

But I do agree that I would use ON td.gift like n.word + '%'

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Ok... bloody variable width font's made it look like the CHARINDEX was based on an Empty String and not a single space, so I did have a misleading bit of information in the answer that I previously posted and took down.

Back on the subject...

matthew.flower wrote:

Sorry but the "correct" answer is wrong. The insert into Numbers is:
INSERT dbo.Numbers (n, word) VALUES (1, 'A '),
Note the white space after A, so the first word is "A ". The suggested join ON:
SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) = n.word
Will fail for the first row because SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) for the row where gift starts with "A partridge..." will return "A" which will not equate to "A ". I am not aware of any collation options that allow a two-character string to equal a one-character string.
While it is not possible to define a function index to aid performance, the join that will work for this low data volume is simply
td.gift LIKE n.word + '%'

I absolutely agree that td.gift LIKE n.word + '%' is a much better answer. But I disagree with your take on the equality failure because of the trailing space.

Trailing spaces are ignored in string comparisons. If they weren't, the things like strings stored in a CHAR() column would be virtually useless because you'd have to Right-PAD the search string to make the two strings exactly equal.

The listed "Correct" answer will work even with the following data for the Numbers table...

CREATE TABLE Numbers (n INT, word VARCHAR(20))
GO
INSERT dbo.Numbers (n, word) VALUES 
 (1, 'A'       + SPACE( 1)),
 (2, 'Two'     + SPACE( 2)),
 (3, 'Three'   + SPACE( 3)),
 (4, 'Four'    + SPACE( 4)),
 (5, 'Five'    + SPACE( 5)),
 (6, 'Six'     + SPACE( 6)),
 (7, 'Seven'   + SPACE( 7)),
 (8, 'Eight'   + SPACE( 8)),
 (9, 'Nine'    + SPACE( 9)),
 (10, 'Ten'    + SPACE(10)),
 (11, 'Eleven' + SPACE(11)),
 (12, 'Twelve' + SPACE(12))
GO

The unfortunate part of this is the cited documentation doesn't actually include any information on that fact because the posted question is a great example of this "feature".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Data_Cat SSC Enthusiast Points: 198 More actions · Answer 6

Data_Cat

SSC Enthusiast

Points: 198

December 23, 2022 at 4:13 pm

#4129433

Thanks for the fun post! Merry Christmas!!

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

BTW... the documentation for the subject of "trailing blanks" NOT being included in comparisons is a bit difficult to find. You would think that MS would have included it in the documentation for at least the CHAR() datatype, but they do not.

Here's the documentation on the subject and more.

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734489 More actions · Answer 8

It does work as the trailing space is ignored. That being said, I'll edit the question as no one should be confused by this. I'll also look to PR some docs to know trailing spaces are ignored.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 9

Jeff Moden wrote:

BTW... the documentation for the subject of "trailing blanks" NOT being included in comparisons is a bit difficult to find. You would think that MS would have included it in the documentation for at least the CHAR() datatype, but they do not.
Here's the documentation on the subject and more. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql

It's worth noting that trailing spaces are included on the RHS of the like operator:

where 'A' like 'A ' is false

where 'A ' like 'A' is true

where 'A' = 'A ' is true

where 'A ' = 'A' is true

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 10

Jonathan AC Roberts wrote:

Jeff Moden wrote:
BTW... the documentation for the subject of "trailing blanks" NOT being included in comparisons is a bit difficult to find. You would think that MS would have included it in the documentation for at least the CHAR() datatype, but they do not.
Here's the documentation on the subject and more. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql
It's worth noting that trailing spaces are included on the RHS of the like operator:
where 'A' like 'A ' is false
where 'A ' like 'A' is true
where 'A' = 'A ' is true
where 'A ' = 'A' is true

Yes... LIKE is one of the exceptions noted in the link I posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

Steve Jones - SSC Editor wrote:

It does work as the trailing space is ignored. That being said, I'll edit the question as no one should be confused by this. I'll also look to PR some docs to know trailing spaces are ignored.

I thought the original question was just fine, especially since it taught a deeper lesson that many are not aware of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 12

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Jeff Moden wrote:
BTW... the documentation for the subject of "trailing blanks" NOT being included in comparisons is a bit difficult to find. You would think that MS would have included it in the documentation for at least the CHAR() datatype, but they do not.
Here's the documentation on the subject and more. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql
It's worth noting that trailing spaces are included on the RHS of the like operator:
where 'A' like 'A ' is false
where 'A ' like 'A' is true
where 'A' = 'A ' is true
where 'A ' = 'A' is true
Yes... LIKE is one of the exceptions noted in the link I posted.

I did a quick find on that page before I posted my comment and there was no mention of LIKE in that article.