The Heap™ – Consultancy ©®

5:37 AM

@Vérace-getVACCINATEDNOW Seven lines: dbfiddle.uk/…. Is that better?

It's not difficult without regex, doesn't require anchors, and will be fast

2 hours later…

McNets

7:26 AM

Morning

John K. N.

7:52 AM

@PaulWhite elegant

Morning

What happens if you add the following value to the table?

('|1 2|3 6|Brokayn|')

I can't run it, because it breaks db<>fiddle for me.

The fiddle I tried be this one

8:09 AM

@JohnK.N. it gives nulls: dbfiddle.uk/…

8:44 AM

@JohnK.N. That came up before. I don't regard 1 2 or 3 6 as a valid integer. One could replace spaces of course, but it seems like madness to me

@JohnK.N. Works for me now. Perhaps the site was having problems earlier.

John K. N.

I was just wondering what would happen. I don't consider them to be ints either.

Yep it's a fair question

The numbers might not even be integers in the source question.

They might be decimal or something.

9:08 AM

or imaginary or complex or surreal or ...

there are kinds of crazy types of numbers ;)

4 hours later…

1:04 PM

A chairde - Morning all!

peacedog

@ypercubeᵀᴹ when you say you would chain CTEs together to get around that limitation we were discussing yesterday, can you give me a better example of what you mean? I tried to think about how to do it last night but couldn't envision it.

oh, it's specific to Postgres. this is not available in SQL Server.

peacedog

ahhh sadge

eg what Cade posted yesterday

WITH NewItem AS (
 ... )
MERGE INTO Item USING NewItem ON 0 = 1
  WHEN NOT MATCHED THEN
    INSERT () VALUES ()
OUTPUT INSERTED.ID, NewItem.SourceID
  INTO whatever

WITH NewItem AS (
 ... ),
ins AS (
  INSERT INTO Item
  FROM NewItem
  RETURNING *      -- the POstgres equivalent of OUTPUT
)
INSERT INTO whatever
SELECT  ins.ID, NewItem.SourceID
FROM ins JOIN NewItem ON ...
;

would be something like that in Postgres ^^

My own PostgreSQL solution to yesterday's problem is here. 2 lines - does require two (not very expensive? at least AFAICS) regexes!

1:16 PM

You can chain multiple "modifying CTEs" (INSERT/DELETE/UPDATE) in postgres that can potentially write into multiple tables.

peacedog

My other option besides using a merge is to add a column to the target table in our DB so I can map back to stuff in the import. I expect I'll wind up going that route but we'll see. Gotta talk to my Boss about it

@Vérace-getVACCINATEDNOW heh, easily broken: dbfiddle.uk/…

1:44 PM

Everyone expects regex to perform well

Unless they've ever used it that is

2:06 PM

@ypercubeᵀᴹ Heh, heh... right back at you once and twice! If our Kiwi friend is allowed use fancy functions, then so can I! Thanks yet again to EB for the second one!

The regex is fine - I mean it is possible that some cretin will be trying to write down his girlfriend's new phone number instead of medical details - but, I think that it is fit for purpose in this case!

@Vérace-getVACCINATEDNOW if it's a form filled by humans long enough, you'll eventually get someone to write his boyfriend's phone number.

it's the human condition ;)

2:59 PM

@Vérace-getVACCINATEDNOW The second one is much better than the first (fails for numbers of length 10 over 2,147,483,647) but negative integers exist you know.

|-123|-456| ought to be valid

I suppose that is the regex?

3:23 PM

Ask and you shall receive! Here it is - these, like any regex can become virtually arbitrarily complicated (1, 2) - but the world is your oyster!

My (rather vague it I'm being totally honest) understanding is that simple enough ones like this are not that resource intensive but they can become so with look-aheads/look-backs and what have you... Maybe as a programmer you (or your boss...) might decide that there's diminishing returns on providing error checking for every conceivable error?

However, my solution has the merit of calculating the integer once on data entry - I could, of course, index these generated columns.

3:56 PM

Could also ensure that the integer doesn't start with a 0! Also, specify a range - 1 - 999. In fact, most dosages/treatments are normally designed to be able to prescribe in small easy quantities - you would never (caveat - virtually never) get a prescription for 437 mg of something! Also, when designing lab protocols for, say, extracting DNA or protein or... the quantities are normally designed in small units of microliters - 0.5μl being about the limit of reliable pipettability!

So, here's my final stab at this one - I'm not going to try and fix any more errors found by inserting weird and wonderful combinations - it's non-zero starting INTEGERs. It's very finicky - took me a while to get that regex bad boy copied and pasted correctly! The interested reader can add range validation using this: ([-+]?[1-9]|[-+]?[1-9][0-9]|[-+]?[1-9][0-9][0-9])...

These pesky regexes can be very addictive - crack cocaine for nerds - as I'm programming, I keep thinking up more and more complex scenarios and how I would deal with them - but, as written above, there's a diminishing return - unless you eat regexes for breakfast, dinner and tea, getting it right and maintaining these nightmares just wouldn't be worth the effort!

@Vérace-getVACCINATEDNOW Is |0|0| a weird and wonderful combination?

The regex seems like a lot of mostly unnecessary complexity. Take the first two split elements and pass them to the try_cast function?

I mean I get it has value if you want to play with regexes, but otherwise it does seem like a forced use.

4:25 PM

No - it's not weird, well, err... yes, it is, but my regex will lead to it failing! I can see why |0|0| would be a perfectly valid measurement - amount of conc. H2SO4 in blood for example! Or 0|50 - Covid detected vs. TB, say? Have it working for 1 - will get back!

First zero going through OK - second failing?

If anybody can pitch in - it's the 3 fields First zero, Second zero and Both zeroes... works for first Zero but not second - am stumpted!

4:43 PM

To hell with this - I've some stuff I've got to do - I'll throw a quick question onto SO and see what I get - there's a few guys over there who are hot stuff when it comes to regexes!

instantly closed as a dupe I bet

Q: Are extra unused columns helpful in future?

4:59 PM

@Vérace-getVACCINATEDNOW 00 is still a zero. at least in American roulette ;)

mustaccio

Currently, while creating tables, we create 4 to 5 extra columns with temp names like c0, c1, c2 etc. to be used later when needed and are filled with NULL by default. This way we don't have to create additional columns when needed. We just rename one of the temp columns create in the first place...

the humanity doesn't fail to impress me every day

Q: Finding the highest number of intersections in between intervals?

@Vérace-getVACCINATEDNOW an interesting problem:

In SQLite, I have a fairly simple data table: Periods(Start, End) I'm storing periods with start and end date (stored as integer in seconds), and I'm looking for the information of the highest number of intersections between all periods. Consider this for easier understanding: Periods are guest...

sqlite

@ypercubeᵀᴹ I'm making some small progress.

@mustaccio far out

@ypercubeᵀᴹ Yep - saw it and upvoted it! The only pity is that I couldn't give the points to you personally since you made it a wiki - but nice!

5:07 PM

we may have had similar questions in the past but couldn't find any

Truly appalling design! I saw that before - except where I saw it, they didn't even bother to rename the columns c01... c20 IIRC - a comment was added to the table! Just to reassure everybody, this is a system that runs the rail network of a large European country!

@ypercubeᵀᴹ Reminded me of dba.stackexchange.com/q/215981/1192

5:20 PM

Pleasing date today 2022-02-02

5:30 PM

@PaulWhite thnx

what I like in this problem is that it's not trivial and there are possibly many different ways to handle it.