Navicat Blog

Counting String Occurrences in SQL June 5, 2020 by Robert Gravelle

Although not as proficient at string manipulation as procedural programming languages such as Java, C++, and PHP, SQL does provide many functions for working with string data. These may be employed to trim off extra spaces or characters, determine how long a string is, and concatenate several field values together. String functions are well worth becoming acquainted with as they can help make your code more effective and readable. In today's blog, we'll learn how to count the number of string occurrences within a char, varchar or text field using a couple of native SQL string functions.

MySQL Default Values: Good or Bad? - Part 2: When To Use Them May 28, 2020 by Robert Gravelle

Part 2: When To Use Them

You probably already know that setting a default value on non-null columns helps get rid of those pesky "Field 'xyz' doesn't have a default value" errors. Hopefully you're also aware that keeping error messages at bay is not in-itself a valid reason for supplying default values. There are many reasons for providing default column values - some good, and some, less so. Part 1 explored the ramifications of MySQL's Strict SQL Mode, as well as how to view and set it using Navicat for MySQL 15. In today's follow-up blog, we'll tackle when to use default values, and how to come up with good ones.

MySQL Default Values: Good or Bad? - Part 1: Strict SQL Mode May 22, 2020 by Robert Gravelle

Part 1: Strict SQL Mode

Getting errors when you don't supply a value for a non-null column can be an immense source of frustration. There's a way to minimize the occurrence of such errors by setting a default value for those columns. Seems like an easy fix, but, as in all things, the devil's in the details. You have to be careful that you don't add a bunch of generic - and useless - data to your tables just for the sake of making INSERTs easier. In today's blog, we'll learn about the ramifications of MySQL's Strict SQL Mode, as well as how to view and set it using Navicat for MySQL 15. In part 2 we'll cover when it makes sense to employ default values (and when it doesn't).

Top N Queries by Group May 14, 2020 by Robert Gravelle

A Top N query is one that fetches the top records, ordered by some value, in descending order. Typically, these are accomplished using the TOP or LIMIT clause. Problem is, Top N result sets are limited to the highest values in the table, without any grouping. The GROUP BY clause can help with that, but it is limited to the single top result for each group. If you want the top 5 per category, GROUP BY won't help by itself. That doesn't mean it can't be done. In fact, in today's blog, we'll learn exactly how to construct a Top N query by group.

Is the Database or Application the Best Place for a Custom Function? May 6, 2020 by Robert Gravelle

Deciding whether do create a function in the database or in application code can be a daunting one. All-too-often, you don't realize that you've made the wrong choice until it's a big hassle to make an about-face. Worse still is the fact that many developers base their decision on whether they're most familiar with SQL or application coding! A better approach is to rely on the strengths of a technology to help guide your decision. In today's blog, we'll break down the decision making process when choosing between a user-defined function (UDF) and one that resides on the application side.

Navicat Blogs
Feed Entries
Blog Archives