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
Ways to Contrast Data
As you have probably determined, with blog posts like these: Ways to Compare DataDifferent Way to Compare Data You know when the word compare is used, sooner or later the opposite is going to be used. Yes, folks – this week we are going to be talking about contrasting data. SELECT * FROM dbo.Person1 EXCEPT…… Continue reading Ways to Contrast Data
A Different Way to Compare Data
Last week, we discussed the INTERSECT operator and how it is a great way to show what data points are the same between two disparate sources. INTERSECT in diagram form As with most operators in SQL Server, there is another way to get the same data. It’s one that many of you are familiar with…… Continue reading A Different Way to Compare Data
Ways to Compare Data
Today’s one of my favorite days – the day we start a new series of posts to help people out with their data! This week, I want to talk about one of the many things that people have asked me to do for them because they think it’s just too hard. This week, I want…… Continue reading Ways to Compare Data