sql – Page 9

How to Add Leading Zeros to a Number in MySQL

February 14, 2020May 16, 2018 by Ian

When working with MySQL, you might occasionally encounter situations where you need to pad a bunch of numbers with leading zeros.

Perhaps you have a requirement that all numbers have three digits, but in the data you’ve been supplied, the numbers range from a single digit, to two, and maybe three digits. Your requirement might be to pad all numbers with leading zeros, but only to make up for any shortfall in the three digit requirement.

The LPAD() function does exactly what you want in this scenario.

MySQL REPLACE() – Replace All Instances of a Substring with another String

November 19, 2019May 15, 2018 by Ian

The MySQL REPLACE() function enables you to replace all occurrences of a substring with another string. It allows you to do things like, replace all occurrences of one word with another word, etc.

This article demonstrates its usage.

Repeat a String Multiple Times in MySQL – REPEAT()

February 14, 2020May 15, 2018 by Ian

This article demonstrates how to repeat a string multiple times in MySQL using the REPEAT() function.

As the name suggests, the REPEAT() function can be used to repeat a string. More specifically, it allows you to specify how many times the string should be repeated.

3 Ways to Find the Position of a Substring within a String in MySQL

November 19, 2019May 14, 2018 by Ian

MySQL has a number of string functions that return the position of a substring within a string. More specifically, they return the position of the first occurrence within the string (or the first occurrence after a given starting point).

The functions I’m referring to are as follows:

INSTR()
LOCATE()
POSITION()

Below is an overview of each one.

How to Find the ASCII Code for a given Character in MySQL

February 14, 2020May 13, 2018 by Ian

In MySQL, you can use the ASCII() function to return the ASCII code for a given character. More specifically, it returns the ASCII code for the leftmost character in a given string.

You can also use ORD(), which works the same way, except that it also supports multibyte characters.

How to Convert Uppercase Characters to Lowercase in MySQL

February 14, 2020May 11, 2018 by Ian

In MySQL, you can use the LOWER() function to convert any uppercase characters to lowercase. Alternatively, you can use the LCASE() function, which is a synonym for LOWER().

The syntax goes like this:

LOWER(str)

Or…

LCASE(str)

Where str is the string you want converted to lowercase.

How to Convert Lowercase Characters to Uppercase in MySQL

February 14, 2020May 11, 2018 by Ian

In MySQL, you can use the UPPER() function to convert any lowercase characters to uppercase. Alternatively, you can use the UCASE() function, which is a synonym for UPPER().

The syntax goes like this:

UPPER(str)

Or…

UCASE(str)

Where str is the string you want converted to uppercase.

How to Reverse the Order of Characters in a String in MySQL

February 14, 2020May 10, 2018 by Ian

If you ever need to reverse the order of a string value in MySQL – that is, return a string with the order of the characters reversed – the REVERSE() function is what you need.

What’s the MySQL Equivalent of STUFF() in SQL Server?

November 19, 2019May 6, 2018 by Ian

If you’re familiar with SQL Server, you might know that you can use the T-SQL STUFF() function to insert a string within a string. As it turns out, MySQL has a similar function – but with a different name.

MySQL’s INSERT() function does essentially the same thing that the T-SQL STUFF() function does.

With the exception of a couple of minor differences (see below), both functions work exactly the same.

How to Insert a String into another String in MySQL using INSERT()

February 14, 2020May 6, 2018 by Ian

In MySQL, you can use the INSERT() function to insert a string into another string.

You can either replace parts of the string with another string (e.g. replace a word), or you can insert it while maintaining the original string (e.g. add a word). The function accepts 4 arguments which determine what the original string is, the position with which to insert the new string, the number of characters to delete from the original string, and the new string to insert.

Here’s the syntax:

INSERT(str,pos,len,newstr)

Where str is the original string, pos is the position that the new string will be inserted, len is the number of characters to delete from the original string, and newstr is the new string to insert.