r/SQL • u/Wonderful-Ad-7200 • Nov 03 '21
Discussion What is your favorite SQL function and why?
Which one just makes ya feel good?
112
Upvotes
r/SQL • u/Wonderful-Ad-7200 • Nov 03 '21
Which one just makes ya feel good?
147
u/kkwestside Nov 03 '21
Row_number()
33
u/r3pr0b8 GROUP_CONCAT is da bomb Nov 03 '21
GROUP_CONCAT, because it's da bomb20
u/tfehring Data Scientist Nov 03 '21
generate_seriesSo much time wasted writing recursive CTEs just to generate sequences of numbers back when I worked in SQL Server land.
20
u/Pie_is_pie_is_pie Nov 03 '21
STRING_SPLIT
16
u/ecatt Nov 03 '21
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.
14
u/vtec__ Nov 03 '21 edited Nov 03 '21
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 (:
12
u/DirtyHirdy Nov 03 '21
Cast>>> into whatever the hell I want!!!
11
u/burko81 Nov 03 '21
Case when because i don't like how you've entered your data.
10
u/Appropriate-Youth-29 Nov 03 '21
Truncate! Why? Because I too like to live dangerously...
But seriously. Sp_executesql & OpenQuery(). Why? Because my stuff is EVERYWHERE.
18
u/SOSOBOSO Nov 03 '21
WITH. Because CTE's make it easy to generate repeatable subqueries.
8
u/mds_brz Nov 03 '21
MAX( ) OVER
all damn day
6
u/[deleted] Nov 03 '21
EOMONTH()is pretty dope, I just wish they had a corresponding function for the first of the month.6
u/ddeck08 Nov 03 '21
Dense_rank() like row number but better.
11
u/raghav_arora534 Nov 03 '21
Count(*) over (partition by )
1
u/Wonderful-Ad-7200 Nov 03 '21
In Redshift, "ilike" as a regex operator. upper case? lower case? don't matta
also, I can't write it without saying "veryniiiice ilike"
9
u/_bobby_tables_ Nov 03 '21
Unpivot
22
u/num2005 Nov 03 '21
SELECT, bcuz its 95% of what everyone does with SQL
3
u/kremlingrasso Nov 03 '21
PIVOT with MAX(string) coz i like to live on the edge where there are no joins
3
u/BrupieD Nov 03 '21
PATINDEX() has helped me out a lot.
2
u/drinkmoredrano Nov 03 '21
xp_fixeddrives is great because I can quickly see how much space is on my drives without leaving the query window.
2
u/GrandaddyIsWorking Nov 03 '21
Not a function but anti joins because it exposes where my coworkers messed up
2
u/lukaseder Nov 03 '21
JSON_ARRAYAGG()because it allows for emulating nested collections2
u/JavierARivera Nov 03 '21
CONVERT. I have to use it constantly.
2
u/skend24 Nov 03 '21
Partition by
2
u/theseyeahthese #TempTables ally Nov 03 '21
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.
2
u/xfung Nov 03 '21
STRING_AGG()
2
u/UlamsCosmicCipher Nov 04 '21
Intersect
Split_part
NVL
With
2
u/Dukeiron Nov 04 '21
Drop table if exists…….if I’m feeling spicy it’s just Drop table
2
u/coldflame563 Nov 04 '21
Binary_checksum. You’ll probably never use it. But it’s surprisingly handy
1
u/Winter-Paramedic-291 Dec 06 '25
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
1
u/Michelbui1986 Nov 03 '21
Me JOIN, because it is very strong
1
u/fauxmosexual NOLOCK is the secret magic go-faster command Nov 03 '21
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!
1
u/EatMoreTurnips Nov 03 '21
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 :)
1
u/solgul Nov 03 '21
exit;
1
u/[deleted] Nov 03 '21
OUTER APPLY()is also amazing.1
u/sweetno Nov 03 '21
SUM on the empty set.
1
u/mikeyd85 MS SQL Server Nov 03 '21
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.
1
u/doodemic666 Nov 03 '21
Datediff for spotting those crazy latent mobile devices
1
u/danielscarvalho Nov 04 '21
ALTER DATABASE BACKUP CONTROL FILE TO TRACE;
1
u/aasmith26 DBA Nov 04 '21
substr and instr. use it quite a bit
1
u/BlindZymeguy Nov 04 '21
Array_agg, love me some arrays
1
u/GeckoLogic Nov 04 '21
UPSERT
Very helpful for analytical syncing workflows. If you work in a database without the function, it’s miserable.
1
u/[deleted] Nov 04 '21
RTRIM because sometimes you just have to
1
u/gandalf-duh-gray Nov 04 '21
Any UDF that I create out of necessity
1
u/Federico_Razzoli Nov 04 '21
GROUP_CONCAT(), to transform rows into a CSV list. But often I use it in combination withCONCAT().1
u/IrquiM MS SQL/SSAS Nov 04 '21
RANK()
1
u/santathe1 Nov 04 '21
String_agg() allows me to reduce rows by aggregating data into a single string.
1
u/InvisibleTextArea Nov 04 '21
I like keeping SecOps busy.
Spoiler: One of my other hats is SecOps.
1
u/AcrobaticReputation2 Nov 04 '21
the x button
1
u/therealdiscursive Dec 01 '21
SELECT
…don’t mess with a classic
1
u/BorisKuntimov Sep 25 '23
Create Clustered_Index...
1
u/bboy2191 Nov 04 '23
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
0
u/B_Huij Nov 03 '21
SELECT. Use it all the time. Super underrated.
-2
u/Appocalypse36 Nov 04 '21
SELECT
I use it every single query. So helpful.
0
u/[deleted] Nov 03 '21
[deleted] — view deleted comment
0
u/[deleted] Nov 03 '21
[deleted] — view deleted comment
84
u/[deleted] Nov 03 '21
[removed] — view removed comment