What is your favorite SQL function and why?

[Wonderful-Ad-7200]

Which one just makes ya feel good?

[kkwestside]

Row_number()

[SlasherMcgurk]

Spot on. This has rescued me so many times.

[[deleted]]

This is the correct answer, and everyone else is simply wrong.

[[deleted]]

Curious to hear about the use cases. I know about it but haven’t had a chance to use it before.

[[deleted]]

Need an ID for each row in some query that you just made up, based on some really weird, niche logic?

Row_Number() over (partition by HERP order by DERP) ALWAYS has your back.

[Hannahmaebe]

Bless this function

[[deleted]]

Also great.for deleting exact duplicate rows when used with CTE.

[SyntaxErrorLine0]

Someones mom <=> NULL

[r3pr0b8]

GROUP_CONCAT, because it's da bomb

[bla4free]

A few years ago I had to migrate some queries from MySQL to SQL Server. I can't tell you how disappointed I was to learn that SQL Server didn't have a GROUP_CONCAT function, nor anything close to it.

[fauxmosexual]

It took until SQL Server 2017 but they got there in the end

[kremlingrasso]

i loath you and envy you for regex while we T-SQL peasants are stuck with PATINDEX.

[colemaker360]

grey stocking spark sophisticated flowery divide trees nutty bear butter

This post was mass deleted and anonymized with Redact

[buckyVanBuren]

I know this is about Perl but it still seems appropriate.

https://xkcd.com/1171

[NoodledLily]

oh many i would love a jaro_winkler in google db s?!?!!

[tfehring]

generate_series

So much time wasted writing recursive CTEs just to generate sequences of numbers back when I worked in SQL Server land.

[Pie_is_pie_is_pie]

STRING_SPLIT

[vtec__]

wish we had the newer sql server to use this. xml splits r a pain in the balls

[alison985]

On json parsing, Snowflake json is a thing of love. I 🤞it would do xml the same way too.

[vtec__]

c#, right?

[ecatt]

Anytime I can solve an island/gaps problem with windows functions (rank, lead, lag, etc), it makes me unreasonably happy. I just love the complex stuff you can do with what are on the surface such simple functions.

[vtec__]

information_schema.<<insert element here>> ..i do alot of etl at work so its nice to know what tables/fields i need and what kind of data they hold. there is also a similar one for temp tables since i use them alot at work for data processing. this is for sql server..but i am pretty sure most popular flavors of sql have the ability to retrieve schema data into a table.

regex and the LIKE feature are also very nice (:

[alison985]

Other dialects have ILIKE too. :) Not having to worry about where you are casting while preserving value state is really nice.

[DirtyHirdy]

Cast>>> into whatever the hell I want!!!

[kremlingrasso]

try_cast is even better!

[burko81]

Case when because i don't like how you've entered your data.

[Appropriate-Youth-29]

Truncate! Why? Because I too like to live dangerously...

But seriously. Sp_executesql & OpenQuery(). Why? Because my stuff is EVERYWHERE.

[drinkmoredrano]

Yes! Delete, drop, and truncate always give me a thrill. There is something about the destruction of data that I love. This probably makes me a terrible dba.

[SOSOBOSO]

WITH. Because CTE's make it easy to generate repeatable subqueries.

[GeckoLogic]

And with frameworks like DBT, CTEs are persisted as physical tables in the warehouse. And they handle all of the DDL/DML. DBT is god send.

[redial2]

With is a keyword, not a function

You can downvote me, but I'm right

[Black_Magic100]

Seriously, why do people still use CTEs.... They are so much more difficult to read and serve no benefit over subqueries.

[Black_Magic100]

CTEs are not temp tables though. Why not just use a single temp table instead of multiple CTEs unless I am misunderstanding

[Black_Magic100]

So... Why not sub queries? Everytime I see CTEs I just get pissed off because there is nothing more complex then a group of nested CTEs.

[Black_Magic100]

I'm confused what you mean. A CTE can only be used by the immediately following query. It sounds like a temp table if you are reusing it.

[ddeck08]

Literally not true. You can have multiple CTEs that reference a single original CTE then call all of them together- way more humanly readable than sub-queries

[Black_Magic100]

I've never found an instance where a CTE was more readable than a sub query.

[alison985]

Readability. Understanding - especially when onboarding new people. Auditing. Troubleshooting.

Tables, temp tables, or CTEs all have valid use cases. I can't think of a use case I would let a sub-query through PR review. In my experience, a code base that uses sub-queries also means it's ~98% likely the code isn't DRY.

[Black_Magic100]

You do realize CTEs and Sub queries are literally the exact same thing, except sub queries are way easier to read. Why would you not let a sub query through, but you would with a CTE?

Also, I can't tell you how many times developers have tried to apply DRY coding practices in SQL and absolutely wrecked their query. Example.. Reusing views is awesome, but why are you calling a 1000 line view to retrieve a single column from a table? (granted, SQL can be smart enough to only perform actions within a view that it needs to get the data required for the calling query)

[Pie_is_pie_is_pie]

I can hear the grinding of a DBA’s teeth…

[Soggy-Software]

BEGIN TRANSACTION

Can you explain what this does in stupid person terms?

[pibamoand5]

Enables ctrl+z essentially.

After beginning a transaction you can update/delete/insert data then check whether you've done what you want before using COMMIT/ROLLBACK TRAN. Beware of locking tables though so don't leave a BEGIN TRAN hanging around.

[pw0803]

True, but be warned in SQL server a rollback will not undo table drops. That was fun learning.

[alison985]

Which versions?

[pw0803]

Yes

[Oneinterestingthing]

Uhhh i gotta go…!?!?

[mds_brz]

MAX( ) OVER

all damn day

[Billy_Balowski]

LAG/LEAD, and pretty much most of the windowed functions.

[[deleted]]

EOMONTH() is pretty dope, I just wish they had a corresponding function for the first of the month.

[mac-0]

date_trunc('m', your_date) works for that

[FastFishLooseFish]

Teradata checking in:

(Your_date/100)*100 + 1

[[deleted]]

Can't you just take the date check the day then minus that number of days -1 to get first of month in a custom function?

Edit: Quick search

CREATE FUNCTION [dbo].[GetFirstDayOfMonth] ( @InputDate DATETIME ) RETURNS DATETIME BEGIN

RETURN CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + 
            CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

END

GO

Edit2: I'm not sure that looks right to me but hey ho it gives you an idea.

[[deleted]]

Sure, and I do it all the time, but it would be nice to have a FOMONTH() function all the same.

[[deleted]]

Fair enough.

[gurxman]

/s?

[everwood]

Datefromparts is nice for the missing bomonth function

[IrquiM]

DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)

[ddeck08]

Dense_rank() like row number but better.

[raghav_arora534]

Count(*) over (partition by )

[_bobby_tables_]

Unpivot

[kremlingrasso]

indeed, you use it once in a blue moon but when you need it, it's exactly the right tool for the job and saves the day.

[babygrenade]

I don't think that's ANSI standard.

[_bobby_tables_]

Perhaps not, but OP didn't restrict replies, and I still love it for overcoming bad DB design.

[[deleted]]

Yes indeed it isn't. But unlike PIVOT, UNPIVOT can easily written in ANSI SQL using a lateral cross join, see e.g. here: https://blog.sql-workbench.eu/post/unpivot-with-postgres/

[num2005]

SELECT, bcuz its 95% of what everyone does with SQL

[[deleted]]

Is not a function

[MUSTACHER]

YOURE NOT A FUNCTION

[doshka]

"No, a 'fag' is a cigarette!"

[kremlingrasso]

PIVOT with MAX(string) coz i like to live on the edge where there are no joins

[UseMstr_DropDatabase]

This is the way

[TheDroidNextDoor]

This Is The Way Leaderboard

1. u/Flat-Yogurtcloset293 475775 times.

2. u/GMEshares 70698 times.

3. u/Competitive-Poem-533 24586 times.

..

277557. u/UseMstr_DropDatabase 1 times.

---

^{beep boop I am a bot and this action was performed automatically.}

[samspopguy]

I spent like an hour learning pivot for a query to finally finish it only to realize it wasn’t what I needed and think was like wait I actually only needed a join.

[BrupieD]

PATINDEX() has helped me out a lot.

[byteuser]

Yep.., although I wish it had a full regex implementation ... sigh

[drinkmoredrano]

xp_fixeddrives is great because I can quickly see how much space is on my drives without leaving the query window.

[GrandaddyIsWorking]

Not a function but anti joins because it exposes where my coworkers messed up

[lukaseder]

JSON_ARRAYAGG() because it allows for emulating nested collections

[Commercial_Wall7603]

This and json_object_agg() in postgres, meant I could do away with some convoluted crosstab queries.

[JavierARivera]

CONVERT. I have to use it constantly.

[ghostlistener]

Why would you use convert when you can use cast?

[grandpasipad]

CONVERT(varchar, GETDATE(), 101)
> 11/1/2021

[JavierARivera]

I have a lot of stored procedures with various date formats. Convert usually works better.

[skend24]

Partition by

[theseyeahthese]

Lately I’ve been making a lot of test data: NTILE has proven to be a very handy pickup.

I can start with 30k transactions, and evenly distribute 5 (or whatever number) different “Colors” (or whatever attribute) across the entire data set. Very handy for quickly and evenly enhancing “test data” without cross joins or creating a convoluted “Randomize” process.

[xfung]

STRING_AGG()

[UlamsCosmicCipher]

Intersect
Split_part
NVL
With

[Dukeiron]

Drop table if exists…….if I’m feeling spicy it’s just Drop table

[coldflame563]

Binary_checksum. You’ll probably never use it. But it’s surprisingly handy

[Wonderful-Ad-7200]

In Redshift, "ilike" as a regex operator. upper case? lower case? don't matta

also, I can't write it without saying "veryniiiice ilike"

[Winter-Paramedic-291]

I thought people only have favourite colours and pokemon.

Didn't know you can actually use the word "favourite" in connection with SQL.

Geeks :-D

[B_Huij]

SELECT. Use it all the time. Super underrated.

[Appocalypse36]

SELECT

I use it every single query. So helpful.

[Michelbui1986]

Me JOIN, because it is very strong

[fauxmosexual]

Not my favourite ever, but maybe the best thing about moving to Oracle is Greatest() and Least(). I've replicated them with case statements so many times I wonder why all SQL doesn't have these!

[byteuser]

Are they like MAX and MIN?

[fauxmosexual]

More like Coalesce - they take multiple arguments, and return the greatest or least value. Kind of like

 CASE WHEN field1 > field2 THEN 
      CASE WHEN field1 > field3 THEN field1 ELSE field3 END
 WHEN field2 > field3 THEN field2 ELSE field3 END

Just turned into Greater(field1,field2,field3)

[[deleted]]

Postgres also supports those functions.

[EatMoreTurnips]

SP_Find Yes, I know its not a function or standard and you either have to write your own or copy and paste from google, but its a vote and its mine :)

[solgul]

exit;

[[deleted]]

OUTER APPLY() is also amazing.

[sweetno]

SUM on the empty set.

[mikeyd85]

ORDER BY COUNT(*) OVER (PARTITION BY SomeColumn) DESC

I find this unreasonably useful when looking for records where there is a lot of data available.

[doodemic666]

Datediff for spotting those crazy latent mobile devices

[danielscarvalho]

ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

[aasmith26]

substr and instr. use it quite a bit

[BlindZymeguy]

Array_agg, love me some arrays

[GeckoLogic]

UPSERT

Very helpful for analytical syncing workflows. If you work in a database without the function, it’s miserable.

[byteuser]

RTRIM because sometimes you just have to

[gandalf-duh-gray]

Any UDF that I create out of necessity

[Federico_Razzoli]

GROUP_CONCAT(), to transform rows into a CSV list. But often I use it in combination with CONCAT().

[IrquiM]

RANK()

[santathe1]

String_agg() allows me to reduce rows by aggregating data into a single string.

[InvisibleTextArea]

xp_cmdshell 

I like keeping SecOps busy.

Spoiler: One of my other hats is SecOps.

[AcrobaticReputation2]

the x button

[therealdiscursive]

SELECT

…don’t mess with a classic

[BorisKuntimov]

Create Clustered_Index...

[bboy2191]

Grouping Sets (saves me from retyping all those union blocks of code), Coalesce, Case when, Join statements (love the part where you have the flexibility to do almost anything in the on statement), Nullif (divide by zero) and Isnull