MySQL – UDF – Validate Integer Function

There is a default function in SQL Server called ISNUMERIC to determine if the string is numeric. In SQL Server ISNUMERIC returns 1 for non numerials also. Ex ISNUMERIC (‘,’) returns 1. To avoid this you can write a user defined function as shown over here. In MySQL, there is no default function like ISNUMERIC as in SQL Server. You need to create a similar user defined function as shown below.

DELIMITER $$
CREATE FUNCTION 'udf_IsNumeric'(number_in VARCHAR(100)) RETURNS BIT
BEGIN
DECLARE
Ret BIT;
IF number_in NOT regexp '[^0123456789]' THEN
SET
Ret:= 1;
ELSE
SET
Ret:= 0;
END IF;
RETURN Ret;
END

The above function uses Regular expression. The expression regexp ‘[^0123456789]‘ will find out if the string has at least one character which is not a digit. The expression not regexp ‘[^0123456789]‘ will just negate that condition so that you get the string which has all characters as a digit.

You can test this using the following example.

SELECT '999' TestValue,udf_IsNumeric('999') NumericTest;
SELECT 'abc' TestValue,udf_IsNumeric('abc') NumericTest;
SELECT '9+9' TestValue,udf_IsNumeric('9+9') NumericTest;
SELECT '$9.9' TestValue,udf_IsNumeric('$9.9') NumericTest;
SELECT 'SQLAuthority' TestValue,udf_IsNumeric('SQLAuthority') NumericTest;

The result is

TestValue NumericTest
 ——— ———–
 999 1
TestValue NumericTest
 ——— ———–
 abc 0
TestValue NumericTest
 ——— ———–
 9+9 0
TestValue NumericTest
 ——— ———–
 $9.9 0
TestValue NumericTest
 ———— ———–
 SQLAuthority 0

You can find more examples about Regular expressions in MySQL over here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

MySQL – How to Format Date in MySQL with DATE_FORMAT()

MySQL supports formatting the datetime values into a different formats using DATE_FORMAT() function. This function accepts date/datetime values as a first parameter and returns into a specific format defined as a second parameter.

Let us explore this with the following examples which are self-explanatory

Define a DATETIME variable
SET @date:='2014-06-16 14:12:49';

-- Display datetime values in YYYY-mm-dd format
SELECT date_format(@date,'%Y-%m-%d') AS formatted_date;

The result is 2014-06-16

-- Display datetime values as Long Date format
SELECT date_format(@date,'%W, %M %d,%Y') AS formatted_date;

The result is Monday, June 16,2014

-- Display datetime values as Full date format
SELECT date_format(@date,'%W, %M %d,%Y %T') AS formatted_date;

The result is Monday, June 16,2014 14:12:49

-- Display datetime values in HH:MM:SS format
SELECT date_format(@date,'%T') AS formatted_date;

The result is 14:12:49

-- Display datetime values in Month Year format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is June 2014

-- Display datetime values in mm-dd-yyyy format
SELECT date_format(@date,'%m-%d-%Y')  AS formatted_date;

The result is 06-16-2014

-- Display datetime values in dd-mm-yyyy format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is 16-06-2014

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – How INSERT() Function Works for MySQL

You know there is an INSERT statement which is used to add data to a table. But did you know that there is an INSERT function which will replace certain characters within a string?

INSERT() function in MySQL is used to insert a substring at the specified position up to the specified number of characters.

Let us consider this example.

set @str:='This is nothing';

The variable @str has a string value “This is nothing”. Suppose you want to change “nothing” into “everything” in this string. You can use INSERT() function as shown below

select INSERT(@str,9,2,'every');

The INSERT() function replaces the characters starting from the position 9 to next 2 characters by “everything”. So in the string part “nothing”, no is replaced by “every” and the full string becomes “This is everything” .

You can also add a string between two strings as shown below

set @str:='This is nothing';
 select INSERT(@str,9,0,'not ');

The result is “This is not nothing”. When the third parameter is 0, it will not replace any characters, but append it at the starting position defined in the second parameter

Note: This INSERT() function is functionally equivalent to SQL Server’s STUFF() function. You can refer the usage of STUFF() function in this post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Add a Column at Particular Ordinal Position in Table

Sometimes you may need to add a column at a particular ordinal position in the table. In SQL Server there is no option to do it via SQL although you can use Designer from SQL Server Management Studio. In MySQL you can do it as part of ALTER TABLE statement.

Let us consider the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Suppose you want to add another column named modified_date between item_id and sales_date, you can use the following command

ALTER TABLE sales ADD COLUMN modified_date DATETIME after item_id;

Now the new column named modified_date will be added after item_id. You can check this using the following command

Field		Type	Null	Key	Default	Extra
--------------------------------------------------------------
sales_id	int(11)	NO	PRI		auto_increment
item_id	int(11)	YES			
modified_date	datetime	YES			
sales_date	datetime	YES			
sales_amount	decimal(12,2)	YES

This is sometimes useful. In relational database the order of column in a table should not matter. The database design and modeling should be such that there should not be any dependency on the order the columns are created inside MySQL table. This is the reason, in SQL Server we can’t do this operation via T-SQL and when we do with the help of SSMS, it practically creates and drop the entire table in the background – (a very bad thing).

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Dynamic SQL with EXECUTE and PREPARE Statements

MySQL supports Dynamic SQL with the help of EXECUTE and PREPARE statements. Suppose you have a scenario where you need to pass table name as parameter value and returns all column values, you can use Dynamic SQL.

Let us create this table and data.

CREATE TABLE TestTable (ID INT, Col VARCHAR(4));
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E';

Now pass the table name as a parameter and returns all rows from it.

SET @table_name:='TestTable';
SET @sql:=CONCAT('SELECT * FROM ',@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

The variable @table_name is assigned name of the table. The variable @sql forms SELECT statement concatenating the Table name so the actual SELECT statement becomes SELECT * FROM TestTable. The PREPARE statement prepares the statement for execution and assigns a name (in this case it is dynamic_statement). EXECUTE command executes the statement prepared by the prepared name dynamic_statement and produces the result. The DEALLOCATE command releases the prepared statement.

So the result is

ID Col
 1 A
 1 B
 1 C
 2 A
 2 B
 2 C
 2 D
 2 E

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Video Course – MySQL Backup and Recovery Fundamentals

Data is the one of the most crucial things for any organization and keeping data safe is the biggest challenge for any DBA. This is true for any organizations. Think about the scenario that you have a database which is extremely important and suddenly you accidently delete the most important table from that database. I am sure this is a very difficult time. In times like this people often get stressed or just make even second mistake. In my career of 10 years I have done often this mistake and often got stressed out due to un-availability of the database backup.

In the SQL Server field, we have plenty of the help on this subject, but in MySQL domain there is not enough help. For the same reason I have build this MySQL course on Backup and Recovery.

Course Outline

Data is very important to any application and business. It is very important that every business plan for data safety. Database backup strategies are often discussed after the disaster has already happened. In this introductory course we will explore a few of the basic backup strategies every business should implement for data safely. We will explore how we can recover our server quickly after any unfriendly incident to our MySQL database. Click to View Course

Here are various important aspects which we have discussed in this course.

  • How to take backup of single database?
  • How to take backup of multiple database?
  • How to backup various database objects?
  • How to restore a single database?
  • How to restore multiple databases?
  • How to use MySQL Workbench for Backup and Restore?
  • How to restore Point in Time for any database?
  • What is the best time to backup?
  • How to copy database from one server to another server?

All of the above concepts and many more subjects are covered in the MySQL Backup and Recovery Fundamentals course. It is available on Pluralsight.

Scenarios

As learning about Backup and Recovery can be very much boring, I decided to create two fictitious characters and demonstrate the entire course based on their conversation. The story is about Mike and Rahul. Mike is Sr. Database administrator in USA and Rahul is an intern in India. Rahul aspires to become a senior database administrator and this is a story about his challenges and how he overcomes those challenges. I had a great time to build this course and I have got very good feedback on this course. I encourage all of you to attempt to learn MySQL Backup and Recovery Fundamental course with this innovative effort. It will be very valuable to know your feedback.

You will need a valid Pluralsight subscription to watch this course.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Introduction to CONCAT and CONCAT_WS functions

MySQL supports two types of concatenation functions. They are CONCAT and CONCAT_WS

CONCAT function just concats all the argument values as such

SELECT CONCAT('Television','Mobile','Furniture');

The above code returns the following

TelevisionMobileFurniture

If you want to concatenate them with a comma, either you need to specify the comma at the end of each value, or pass comma as an argument along with the values

SELECT CONCAT('Television,','Mobile,','Furniture');
SELECT CONCAT('Television',',','Mobile',',','Furniture');

Both the above return the following

Television,Mobile,Furniture

However you can omit the extra work by using CONCAT_WS function. It stands for Concatenate with separator. This is very similar to CONCAT function, but accepts separator as the first argument.

SELECT CONCAT_WS(',','Television','Mobile','Furniture');

The result is

Television,Mobile,Furniture

If you want pipeline as a separator, you can use

SELECT CONCAT_WS('|','Television','Mobile','Furniture');

The result is

Television|Mobile|Furniture

So CONCAT_WS is very flexible in concatenating values along with separate.

Reference: Pinal Dave (http://blog.sqlauthority.com)