Sometimes you find yourself stuck in a rut and you never even know that there is a rut. That’s where I found myself today my friends! For years – and I do mean years – when I’ve been asked for the first 10 rows from a query, I would do the following: SELECT TOP 10…… Continue reading TOP of the Mornin’ to You!
Author: Kevin
More Fun with Smushing Data Together
Last week, we learned all about the old-style string concatenation and the new-ish, hip CONCAT function. This week, it’s time to talk about the CONCATs lesser talked about, but equally helpful cousin – CONCAT_WS. With CONCAT, we formatted the person’s name in a LastName, FirstName format using the following query. SELECT CONCAT(TRIM(LastName), ‘, ‘, TRIM(FirstName))…… Continue reading More Fun with Smushing Data Together
Sometimes You Need to Push Strings Together
Every now and again, you’re probably going to have to slap a few strings together in your fun with SQL. A long time ago (20 years ago), in SQL Server world, there was only one way to do it – and sadly, you’ll probably see many developers still doing it this way. SELECT LastName +…… Continue reading Sometimes You Need to Push Strings Together
Time to give the data a TRIM()
For years, when it came to getting rid of the spaces at the beginning/end of a string, you were stuck with using the functions LTRIM and RTRIM – meaning left trim and right trim respectively. For many versions of SQL Server – and probably quite often even in code written today – you will see…… Continue reading Time to give the data a TRIM()
Something to Remember with APPLY
I bet y’all thought that we had completely exhausted the topic of how to use APPLY – whether it be CROSS APPLY or OUTER APPLY. Well, guess what? Nope. Just nope. The word to remember when using either of the APPLY operators is deterministic. A deterministic function, as defined by O’Reilly, “always returns the same…… Continue reading Something to Remember with APPLY
CROSS vs OUTER: Knockdown drag-out free for all
In this corner, with a total of 10 letters – we have CROSS APPLY. In the other corner, also with a total of 10 letters – we have OUTER APPLY. Yes, that’s right. From the outside, there’s truly not a lot of difference between the two apply operators. Both of them work very well with…… Continue reading CROSS vs OUTER: Knockdown drag-out free for all
STRING_SPLIT is to be used with what?
My friends! Last time together, we discussed using the STRING_SPLIT function and how it’s used in combination with the CROSS APPLY. First off, most of us are used to working with an INNER JOIN instead of CROSS APPLY. Well, you’re not going to be able to use an INNER JOIN when you’re using the STRING_SPLIT…… Continue reading STRING_SPLIT is to be used with what?
Have Separated Fields? Make Them Rows!
Sometimes, you’ll have a lot of comma-separated data in your databases. Maybe you’ll have lots of data shoved into one field separated only by a vertical bar. As we all should be asking, “How do I get that data out of that one field and separated into several rows?” Well, I’m glad you asked, my…… Continue reading Have Separated Fields? Make Them Rows!
Opposite of a Left Anti Join
If you followed along with last weeks’ post (found here), then the title of today’s post hopefully makes sense. If not, then I would highly recommend reading it (or re-reading it) so that we all can be on the same page. Read it? Great. Let’s get started. Last week I left you with the following…… Continue reading Opposite of a Left Anti Join
A New Way to Contrast Data
If you’ve been following along with the previous blog posts, you’ll know I like to show different ways to get the same data. With last week’s post on how to find where two tables differ – found here – you know that this week we’re going to see another way of seeing what is shown…… Continue reading A New Way to Contrast Data