The Heap™ – Consultancy ©®

Paul White

06:26

@Charlieface but LEN doesn't count trailing spaces?

I get the intention of course, I just don't see how it makes sense in that context

Charlieface

10:54

@PaulWhite Fair enough, DATALENGTH it is.

Paul White

That counts bytes, not characters

Charlieface

11:05

I know, but for varchar it's the same thing. Either way, somehow or other the point needs to be made that that is what should be in the parameter for STUFF, not blindly putting in 1 every time

If you want to edit it differently then go ahead, but I think it shouldn't stay as it is.

Paul White

varchar can be UTF8 now

never mind that column names are natively unicode/ucs-2/whatever

character length is something that has always bugged me about T-SQL

Paul White

11:27

dbfiddle.uk/…

but if you're using 😀 as a delimiter, good luck to you I guess

As a less trivial example the character £ needs one byte for varchar where the character is available on the code page, but two bytes for varchar UTF8

The answer mixes single-byte and multibyte string encoding in many places already. I don't know that hardcoding ',' and a length of one is all that bad. Perhaps it's possible to write a parameterized delimiter simply and correctly for all cases, but I don't immediately see it.

I don't even recall off the top of my head if code point 32 is the only trailing character ignored by LEN.

Jun 22 at 14:33, by Paul White

fundamentally though, strings were a mistake

Charlieface

12:27

@PaulWhite Hmm, seems STUFF just counts it as one character dbfiddle.uk/…

@PaulWhite I agree, should have been denormalized into separate rows, one character on each row 😀

Paul White

@Charlieface Yes I think all the common string functions have been updated to handle SC/UTF8 properly. The issue was DATALENGTH though.

The common workaround for LEN is LEN(@String + N'X') - 1

Which is fine as long as it's OK to add a character to the string

idk how it scores on elegance either

Charlieface

No DATALENGTH is correct, it's a 4-byte character. LEN returns the number of characters, DATALENGTH returns the number of bytes.

Paul White

Yes I know

Charlieface

Oh I see you mean issue with my solution using DATALENGTH

Paul White

Yes

Why they couldn't have provided a general character-length function, I do not know

Blame the SQL Standard I suppose

Charlieface

12:35

Why can't we just have one string type, with one code page, with either truncated or not truncated spaces consistently? Like UTF-8 or UTF-16? I guess I'll add it to my list of things that SQL has made an absolute hash of, where other languages seem to manage quite alright.

Paul White

Maybe a super-verbose version of LEN is in the works 🤣

Charlieface

LEN(YourString IGNORE WHITESPACES) and LEN(YourString RESPECT WHITESPACES) coming your way

Paul White

yep

Charlieface

Sorry LEN(YourString) WITH (IGNORE_WHITESPACE = ON, WHITESPACE_CHARS = ' \N\R\T')

Paul White

though the full syntax would provide options for what counts as white space as well

like .NET

Charlieface

12:39

Naah, .NET does it much better. YourString.Length does what it says on the tin, there is only one code-page (UTF-16) and spaces are not trimmed (why on earth would they be??)

Paul White

I was thinking of the trim syntax I think

Charlieface

YourString.Trim('\t', '\r', '\n') makes much more sense than the usual SQL garrulousness

Aug 29 at 19:20, by Charlieface

@mustaccio Nah, like I said we need to sweep away the whole thing. I would love a real object-oriented database language and design, where I can actually use functions on objects and the database can work that into a query plan. Something like Linq, but not missing half the features (window functions I'm looking at you).

Paul White

> The .NET Framework 3.5 SP1 and earlier versions maintains an internal list of white-space characters that this method trims if trimChars is null or an empty array. Starting with the .NET Framework 4, if trimChars is null or an empty array, the method trims all Unicode white-space characters (that is, characters that produce a true return value when they are passed to the IsWhiteSpace(Char) method).

> Because of this change, the Trim() method in the .NET Framework 3.5 SP1 and earlier versions removes two characters, ZERO WIDTH SPACE (U+200B) and ZERO WIDTH NO-BREAK SPACE (U+FEFF), that the Trim() method in the .NET Framework 4and later versions does not remove.

> In addition, the Trim() method in the .NET Framework 3.5 SP1 and earlier versions does not trim three Unicode white-space characters: MONGOLIAN VOWEL SEPARATOR (U+180E), NARROW NO-BREAK SPACE (U+202F), and MEDIUM MATHEMATICAL SPACE (U+205F).

docs.microsoft.com/en-us/dotnet/api/…

Humans mess everything up

Charlieface

Yeah, they fixed it, breaking changes be damned, because it was wrong. Not maintain compatibility because oh someone still has an app that uses SQL Server 7.0 and still needs to work with no modifications.

Paul White

No doubt. My point is all implementations have quirks due to humans being humans. Some handle the process of improvement better than others for sure.

But it would be a bold choice to remove/change varchar.

I don't expect many people care about zero-width spaces etc.

Charlieface

12:46

Stuff at least gets fixed properly, rather than "if you install non-CU-included hotfix X and enable TF Y" or "if you happen to be using any of the ANSI_...=OFF options then everything breaks (indexed views I'm looking at you)

Paul White

What on earth is a MEDIUM MATHEMATICAL SPACE anyway

mustaccio

It's where the Schrödinger's cat lives

Charlieface

Anyway, enough ranting on a Sunday. Sorry just getting depressed because it looks like our annual three days of summer is over.

Paul White

Brace yourself etc.

Charlieface

We don't actually have winter here (that at least would be interesting), we have 10 months of autumn, dead leaves and all.

Paul White

12:53

ha ha

Charlieface

When I was a kid we had a foot of snow a few times. Don't think there's been that since 2013-14, just a scattering of fluff that melts in an hour

ypercubeᵀᴹ

@PaulWhite I was going to ask just that!

Paul White

Presumably a compromise between advocates of SMALL MATHEMATICAL SPACE and LARGE MATHEMATICAL SPACE

ypercubeᵀᴹ

@Charlieface where is here?

Charlieface

Hilbert space

In mathematics, Hilbert spaces (named after David Hilbert) allow generalizing the methods of linear algebra and calculus from (finite-dimensional) Euclidean vector spaces to spaces that may be infinite-dimensional. A Hilbert space is a vector space equipped with an inner product which defines a distance function for which it is a complete metric space. Hilbert spaces arise naturally and frequently in mathematics and physics, typically as function spaces. The earliest Hilbert spaces were studied from this point of view in the first decade of the 20th century by David Hilbert, Erhard Schmidt, and...

@ypercubeᵀᴹ UK, north west and north east

Paul White

12:56

Jul 31 at 11:36, by Charlieface

Now I'm in the North West, where Liverpool, Manchester, Bradford, Stoke and Sheffield are all about 40 miles from each other and have entirely different accents.

Charlieface

Also relevant

codeblog.jonskeet.uk/2014/12/01/… "When is an identifier not an identifier? (Attack of the Mongolian Vowel Separator)"

@PaulWhite Gotta be careful here, I'll be giving out my DOB and bank details next...

Paul White

The transcript knows all

Still less than Google tho

@Charlieface Good read. Everything's broken if you dig deep enough.

Feb 10, 2018 at 18:15, by Paul White

Amazing anything ever works frankly

ypercubeᵀᴹ

@Charlieface not higly relevant but ok

apparently there are 17 members in the Unicode "White Space" class: compart.com/en/unicode/bidiclass/WS

Paul White

13:16

depending on the Unicode version (see Jon Skeet link just above)

@ypercubeᵀᴹ Ah well you see we were talking about mathematical spaces and a Hilbert space is a type of mathematical space (not a character, a thing). HtH. /JEAGL

Joke improved.

Vérace

16:07

A chairde - Morning all!

Zikato

Morning

Vérace

Quick question - mind fried. With Oracle, how do I get 1/10 = 0, 2/10 = 0... 10/10 = 1, 11/10 = 1... 20/10 = 2, 21/10 = 2... &c.

Zikato

@Charlieface I've used to live in Stoke for 3 years. I can confirm the no snow. I envied the UK because it had only spring and autumn. For me, it beats the extremes of only summer and winter.

Vérace

And what's this called? I've searched and don't have much for my troubles. Also, with Oracle, I seem to attract the ORA-00907: missing right parenthesis error like rotting fruit attracts flies!

Charlieface

@Vérace It's called Integer division. I think Oracle you do a DIV b see docs.oracle.com/cd/E17952_01/mysql-5.7-en/…

Vérace

16:14

@Charlieface - I think that's MySQL? Check here. I've done that myself! :-)

ypercubeᵀᴹ

@Vérace integer division

For Oracle, you'd have to do cast(3 / 10 as int)

Vérace

@ypercubeᵀᴹ - that's what I thought - it's called that in PostgreSQL - but I searched and found nada! Ahhh... CAST me sister!! Comment ca va a Geneve le dimanche apres-midi? J'espere que tu t'amuses bien!

ypercubeᵀᴹ

@Vérace indeed. MySQL docs can be found now under docs.oracle.com which is rather confusing, to humans and search engines ;)

Vérace

I once based an answer on mixing up MySQL and Oracle docco!!!! Humiliating wasn't in it!

:-)

ypercubeᵀᴹ

You could also cast to numeric(X,0)

Vérace

16:18

I think I'll go with INT to be sure, to be sure!

Charlieface

@Vérace You're right TRUNC(a / b) should do the trick, or cast to int

Vérace

I'm answering this - my previous answer isn't great - and the fiddle is PostgreSQL - couldn't get the Oracle one to work - I think I have what I need now. Have a way better answer using window functions - but it required me getting to grips with window frames - not my forte! Spent all day on this - but I learnt a shedload!

Paul White

TRUNC(x, -1) seems neatest

Vérace

Can't understand how TRUNC(1, -1) = 0! dbfiddle.uk/…

Paul White

16:33

> The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.

dbfiddle.uk/…

Now I looked at the question and that's exactly how the accepted answer does it

ypercubeᵀᴹ

Erik truncates everything with -2. So many invoices, he carries only 100-dollar bills.

2

Vérace

I want n2 to be 0 for 10 places, 1 for 10, 2 for 10! I can do it standing on my head in PostgreSQL. Did Larry Ellison do any crypto while working for the CIA? Given Oracle's error messages, it wouldn't surprise me!

Accepted answer is very elegant! However, I think window functions might be the way to go - suppose you want to do something every 7 days and not 10? How would TRUNC cope with that?

Paul White

simply use base 7 instead of 10

@Vérace dbfiddle.uk/…

ypercubeᵀᴹ

I don't see why one would need anything but n2=-2 for that question. They want to split in 100s

Vérace

Googled this "oracle database window function frame clause" - came straight back at me with MySQL 8 docco! Jesus!

I know the OP asked about 100s - but, I can easily imagine a scenario where a cycle of 7 would be desirable!

PW - your fiddle doesn't appear to split stuff by 7!

ypercubeᵀᴹ

16:43

@Vérace trunc(X, -2) is a shortcut for trunc(X / 100) * 100. Adjust for 7 accordingly ;)

Paul White

@Vérace Yes that was a joke. The fiddle shows what you asked for I think

Vérace

Use MOD or % or something like that - I'll bung that into my answer!

Paul White

@ypercubeᵀᴹ I hope that message used mathematical middle spaces

Vérace

I'd prefer a solution based on Mersenne primes!

That are divisible by 7!

:-)

Paul White

Logarithms seem the way to go

Vérace

17:10

Done and dusted - phew! I worked on that baby for a while - but it was great. My first real foray into window function frame clauses - I have read a bit about them - I highly recommend Bruce Momjian's video presentation(s) on EnterpriseDB.com - his one on window functions is really excellent! And, I got it to work on Oracle - yaay! Thanks for the assistance guys!

p.s. I'm still working on a Mersenne prime solution!

ypercubeᵀᴹ

17:26

@Vérace you first have to find one that is divisible by 7 ;)

Vérace

17:56

@ypercubeᵀᴹ The C programme that I wrote on my phone last night in the pub has already found 3 of them and several more that are divisible by other numbers - I've even found one that's divisible by 2!

Erik Darling

18:52

@Vérace but did you send any invoices?

Vérace

I'm hoping to patent my programme and sell the rights to one of the FAANGs for a tidy sum!

Erik Darling

Nice

Can I invest in it now

Vérace

I also have a nice bridge in the middle of Paris that might be of interest for your portfolio?

Erik Darling

19:16

You can’t possibly have that bridge I already bought it from a mime.

Vérace

19:27

You can't possibly own it! My great-great-great grandfather Napoléon is buried in the building visible at the end (Les Invalides - formerly a military hospital) and the bridge has been in our family for over 200 years. However, we've now fallen on hard times (too many blockchain company shares), so we're looking to sell! I can offer you a shareholding for a reasonable fee!

Erik Darling

19:57

I’ll trade you a program with a Marsenne prime solution for it, this guy I know is working on one.

Vérace

20:10

I've already written a C programme which can find factors for Mersenne primes (pub, last night - see above) - it was written on my circa 2000 Blackberry which is beer... err... water proof!

If I ever, ever, ever, ever again try to answer a question here touching on MySQL regular expressions, please have me incarcerated for a long, long, long time in a secure psychiatric facility which has full access to Class A drugs... err... I mean medication. I'm a bit like an addict - I vow I will never touch one again, but then I just can't resist one more go at chasing the dragon!

Check this out! You can alternate between PostgreSQL (a sane system) and MySQL (a system driving me insane) - it's the exact same regex - and a simple one to boot!

FTR, I was trying to answer this!

Erik Darling

@Vérace that was the joke 🥳

Vérace

20:26

@ErikDarling I'm watching tv with my mother, chatting with you and trying to write regexes at the same time - no wonder I make mistakes - forgot to REVERSE() the MySQL strings! Got it to work - yaay!

Erik Darling

20:45

@Vérace hi mom!

Vérace

I'll tell her you said hello! :-) She says hello back!

ypercubeᵀᴹ

21:41

@John This is not how sorting (especially Unicode sorting/comparison) or indexes work. I am working on an answer now.. — Solomon Rutzky 1 min ago

it took him an hour ;)

ypercubeᵀᴹ

23:35

David Browne intercepted with a faster ~~goal~~ answer!

where we learn that (unintuitively) we can have a,b,c where a = b , a LIKE c and b NOT LIKE c are all TRUE.

The above uses BROKEN MATHEMATICAL SPACE ;)

Transcript for

The Heap™ – Consultancy ©®