Getting the main course right

Question

Post reply

Getting the main course right

Steve Jones - SSC Editor

SSC Guru

Points: 736252
More actions
November 20, 2018 at 9:39 pm

#416508

Comments posted to this topic are about the item Getting the main course right

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

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

HappyGeek SSCoach Points: 18729 More actions · Answer 1

Interesting, learnt something new, thanks Steve.

...

Stewart "Arturius" Campbell SSC Guru Points: 72626 More actions · Answer 2

Really interesting question, thanks Steve.
looks like a very powerful function to have in one's toolbelt

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Frank Boje Old Hand Points: 360 More actions · Answer 3

The answer C is right, when collation of server is "case insensitive" (by default when istall SQL Server). In case of collation is "case sensitive", you get "I waip po have appllpie lor dpiier" and all answers are wrong!

g.maxfield Default port Points: 1471 More actions · Answer 4

g.maxfield

Default port

Points: 1471

November 21, 2018 at 3:06 am

#2014188

Hi

I got an error!?

Stewart "Arturius" Campbell SSC Guru Points: 72626 More actions · Answer 5

g.maxfield - Wednesday, November 21, 2018 3:06 AM
Hi
I got an error!?

TRANSLATE is only available from SQL2017

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

morlindk SSCrazy Points: 2294 More actions · Answer 6

morlindk

SSCrazy

Points: 2294

November 21, 2018 at 3:22 am

#2014192

Hilarious!

g.maxfield Default port Points: 1471 More actions · Answer 7

Thanks - that would explain it.

We're obviously behind the times!

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 8

fun little function

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

webrunner SSC-Dedicated Points: 31865 More actions · Answer 9

Thanks for the question, I learned something about this wacky function.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Revenant SSC-Forever Points: 42467 More actions · Answer 10

Very witty - thank you, Steve. And have a happy Thanksgiving!

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 11

Revenant - Wednesday, November 21, 2018 9:33 AM
Very witty - thank you, Steve. And have a happy Thanksgiving!

Thanks, you, too.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Heh... STUFFing on steroids!

For those in the U.S.A, have a great Thanks Giving! Everyone else... have a great weekend when it starts.

And, Steve, thanks for what you do for all of us.

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

ildjarn.is.dead Ten Centuries Points: 1164 More actions · Answer 13

SSMS 17.9 and SQL 2019:

Funny thing is that the query runs (and gives the expected result).

Toby Ovod-Everett SSC Enthusiast Points: 156 More actions · Answer 14

The documentation for TRANSLATE on Microsoft's site is misleading. Luckily, it's incorrect, because the actual behavior is far more useful than the claimed behavior. The documentation claims:

The behavior of the TRANSLATE function is equivalent to using multiple REPLACE functions.

This is only the case if none of the characters in the third parameter to TRANSLATE show up in the second parameter in an earlier position. For instance, assume you wanted to use TRANSLATE to map a to d, b to c, c to b, and d to a. In this situation, if one uses multiple REPLACE functions, the first REPLACE is going to map all of the a characters to d characters, but the final REPLACE will map both original d characters and original a characters that got mapped to d. The following code snippet illustrates this issue along with an ugly workaround.
SELECT Inp, TRANSLATE(Inp, 'abcd', 'dcba') AS Translate_abcd2dcba, REPLACE(REPLACE(REPLACE(REPLACE(Inp, 'a', 'd'), 'b', 'c'), 'c', 'b'), 'd', 'a') AS Replace_a2d_b2c_c2b_d2a, REPLACE(REPLACE(REPLACE(REPLACE(Inp, 'd', 'a'), 'c', 'b'), 'b', 'c'), 'a', 'd') AS Replace_d2a_c2b_b2c_a2d, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Inp, 'a', CHAR(1)), 'b', CHAR(2)), 'c', CHAR(3)), 'd', CHAR(4)), CHAR(1), 'd'), CHAR(2), 'c'), CHAR(3), 'b'), CHAR(4), 'a') AS Replace_a21_b22_c23_d24_12d_22c_32b_42a FROM ( VALUES ('abcdef_adbecf') ) AS V(Inp);
Note that the workaround relies upon the existence of characters that can be guaranteed to not be in the input string. I'm assuming that 0x01, 0x02, 0x03, and 0x04 don't exist, but that isn't always a safe assumption.

P.S. I am aware that I can work on submitting a pull request with update documentation - hopefully I'll have a roundtuit someday for that!