SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Lib and let die: Aug 26, 2004

I may be chasing down something that just isn't possible with SQL (T-SQL, to be specific) but I'm trying to disentangle records with a '&' delimited between two first names, and then create two rows from that process, one for each person.
It's...almost working, but I'm not playing horseshoes so I'm not happy with almost.

My sample data is as follows:

code:

ID | First | Last  | Envelope
1  | John  | Smith |John & Jane Smith
2  | Eric  | Andre | Eric & Diana Andre

What I've written up so far

SQL code:

BEGIN

DECLARE @MinRows INT
DECLARE @MaxRows INT
DECLARE @CurrentRow INT = 1
DECLARE @NameString NVARCHAR(MAX)

SELECT @MaxRows = MAX(ID) FROM Contacts
SELECT @MinRows = MIN(ID) FROM Contacts

WHILE @CurrentRow <= @MaxRows
	BEGIN
		SELECT @NameString = Envelope FROM dbo.Contacts WHERE ID = @CurrentRow;
		INSERT INTO ContactsCleaned
		SELECT ID, value, Last, Envelope FROM dbo.Contacts
		CROSS APPLY STRING_SPLIT(@NameString, '&');
		SET @CurrentRow = (@CurrentRow +1)
	END;
END;

Somewhere in here, something is happening more than once. If I run a SELECT on my inserted table, I get these results:

There are more cleanup steps I have planned but I'm also starting to think at this point that maybe I want to route my data through some python code to manipulate it instead?

Ultimately my goal is to have a result table that has an entry

code:

1 - John - Smith - John & Jane Smith
1S - Jane - Smith - John & Jane Smith

and so on

Am I just barking up the wrong tree with SQL for this kind of thing?

# ¿ Jan 25, 2023 17:52

Adbot: ADBOT LOVES YOU

# ¿ May 21, 2024 06:42

Lib and let die: Aug 26, 2004

Thanks! Most of the cleanup I need to apply is covered by the replace and trim operators so that's one less checkbox for me! Is there a particular functional reason for using a table variable rather than SELECTing right from my already-populated Contacts table?

# ¿ Jan 25, 2023 19:00

Lib and let die: Aug 26, 2004

Macichne Leainig posted:

I asked it to give me a null cipher once, and I poo poo you not, it basically told me "the first letter of each word from the sentence 'the dog ran away' spells a message. for example, 'the,' dog', and 'ran' spells "HELP" "

I really wish I had the confidence chatgpt has

"If you can't dazzle them with brilliance, baffle them with bullshit," is surprisingly human

# ¿ Feb 18, 2023 01:37

Lib and let die: Aug 26, 2004

kumba posted:

That was my first guess, but it does not! i can select distinct bs.Month for the timeframe and there's nothing weird at all - just a list of 6 character values that I would expect to see!

Does including a

code:

WHERE TRY_CAST(bs.Month AS Date) IS NULL AND bs.Month IS NOT NULL

return any specific results?

is the schema using something weird like SMALLDATETIME?

# ¿ Aug 16, 2023 16:16

Lib and let die: Aug 26, 2004

I will occasionally tap into ChatGPT when I need my statements to bridge the gap between "clever" and "fancy", and did so this morning when working with some data exported from salesforce (my goal was to take each Account with a type of 'Household' from the Accounts file and populate, in-line, the primary household member (indicated in the one2oneContact field as per usual SF) and all associated members of the household identified by the [AccountId] in the Contacts file - the insert script for this particular item in our database is built around the expectation that we are getting data in the form of [HouseholdID][Member1][Member2][Member3] and so on - suffice to say it needs some serious reworking but that's neither here nor there).

Anyway, chatGPT gave me a fine output. It might not be the most efficient way to do it, but it gives me the results I want. I've got to export the results to excel and do a text to columns then re-import it, but it's still way less annoying than it could be.

My question, ultimately, is with ChatGPT's followup 'notes' or whatever.

quote:

In this query, the ContactConcatenation CTE uses the STUFF and FOR XML PATH('') technique to concatenate the secondary contact IDs into a single column.

What exactly is the "STUFF and FOR XML PATH('') technique" and what is it doing?

I can provide the full output here, but the relevant code bit is this:

code:

SELECT
        pc.AccountID,
        STUFF((SELECT ', ' + CAST(sc.ContactID AS VARCHAR(500))
               FROM SecondaryContacts sc
               WHERE pc.AccountID = sc.AccountID
               FOR XML PATH('')), 1, 2, '') AS SecondaryContactIDs
    FROM PrimaryContacts pc

pc and sc are aliases assigned to CTEs for primary and secondary contacts defined a bit further up in the output, for reference

# ¿ Aug 21, 2023 21:45

Lib and let die: Aug 26, 2004

And there's the problem with ChatGPT: I can use STRING_AGG lmfao

# ¿ Aug 21, 2023 23:47

Lib and let die: Aug 26, 2004

Yeah I could have really improved my prompt there in retrospect.

# ¿ Aug 22, 2023 00:07

Lib and let die: Aug 26, 2004

I'm far from a seasoned DBA but so far as working in a client onboarding/ETL capacity goes I sure appreciate it when id columns are named thing_id because it makes mapping data from legacy systems more intuitive (ask me about staring into the horrors of a raiser's edge backup file)

# ¿ Oct 27, 2023 00:58

Lib and let die: Aug 26, 2004

Just-In-Timeberlake posted:

lmao the database I inherited would give you all nightmares

[laughs in democratic party fundraising and engagement database]

# ¿ Oct 27, 2023 01:15

Lib and let die: Aug 26, 2004

shame on an IGA posted:

oh poo poo I think I'm one of your users

was.

have a backup plan ready.

# ¿ Oct 27, 2023 02:06

Lib and let die: Aug 26, 2004

I would blow Dane Cook posted:

With halloween just been and gone, what's your spookiest database experience?

I mentioned NGP VAN, right?

e: "a client needs the disclosure report updated for this batch of thousands of donations."

"ok, where do those values live?"

"no one knows. make the change in the front-end and see what updates."

"I can't change the front end. The batch is closed."

"Yeah."

Lib and let die fucked around with this message at 14:28 on Nov 6, 2023

# ¿ Nov 6, 2023 14:19

Lib and let die: Aug 26, 2004

Heh, mini-Vinnie

# ¿ Nov 11, 2023 17:01

Lib and let die: Aug 26, 2004

GigaPeon posted:

Also there�s so much AI poo poo. How are people just cool with hooking up their environments to the internet and sending it to a third party?

Look, we have to take risks, it's part of doing business.

At least, that's what Scott Brighton, CEO of Bonterera, parent company of NGP VAN, the Democrat Party's purpose-built voter engagement and fundraising platform has to say about leveraging AI.

God I can't wait 'til '24 kicks into high gear and the bottom falls out.

# ¿ Nov 17, 2023 13:48

Lib and let die: Aug 26, 2004

Commas before, you filthy animals

# ¿ Nov 21, 2023 19:15

Adbot: ADBOT LOVES YOU

# ¿ May 21, 2024 06:42

Lib and let die: Aug 26, 2004

SQL Prompt is really handy for refactoring but I don't know that I'd pay redgate prices just for that. We were always told that its use was non-optional because of stuff like detecting UPDATE or DELETE statements without WHERE clauses and less about making our code pretty.

# ¿ Nov 21, 2023 20:24

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'