Before tackling the differences, let's recap the similiarities: They're all used to locate one string within another by identifying its first positon. 2. Unlike their Excel counterparts, they will all return 0 if the substring under scrutiny cannot be found To better illustrate the differences between the functions, I'll be using the 'TED Talks' dataset from … Continue reading What is the difference between….all the functions that find a substring 1st position?
Although rarely, it could happen that you end up with fully capitalized strings in SQL. Whilst there's often nothing wrong with the data quality, it's not very appealing to be showing the users of your analyses data in capital letters. Personally, I always get the feeling of being shouted at when I see fully capitalized … Continue reading How do I change the case of a string with SQL?
It has often been the case in the course of my assignments that I've had to do quite some data prep in Excel before I could have a clean dataset to crunch. If you also need to do this in Excel, below are 10 functions that I have found indispensable. The datasets I'm going to … Continue reading 10 Functions that Help you Crunch Text Strings in Excel
No, I still haven't forgotten I promised to get an example of a text string analysis that applies to SQL Server and PostgreSQL Happy SQL-ing!
Need a quick re-cap of some of the text functions in SQL? By using a combination of them, you can: count the number of words in a string find if a string starts with a number find if a string contains a particular punctuation sign find if a string ends in a particular punctuation sign … Continue reading How do I analyse a string length and punctuation with SQL?
It was pretty cool that the substring() function had the exact same syntax in all SQL Server, MySQL and PostgreSQL Are there more functions like that? Check out the ones below! replace() = replaces one part of a string with another ltrim() = removes leading spaces from a string rtrim() = removes trailing spaces from a string have … Continue reading How do I replace a string in SQL and make sure it has no extra spaces?
It's quite simple if you're using MySQL or PostgreSQL - the function you need in both is simply - length() There are a couple of points you'd better keep in mind if you're using SQL Server, however. 1. The function in that environment is called len() 2. Unlike length in the other two database systems, … Continue reading How do I determine the length of a string with SQL?