MySQL Query – Selecting Default Value Returns Blank

MySQL

I am trying to select the default value if a row do not have a value with this snippet

1

SELECT IFNULL((SELECT as_title FROM all_scheduled_messages WHERE id = 1 LIMIT 1) ,'lorem ipsum');

When i run the query,it returns a blank and so is when i

2

SELECT as_title FROM all_scheduled_messages WHERE id = 1 LIMIT 1

Why is my first query returning the text i want as default?.

Best Answer

I think you're going about this all wrong.

You want to wrap IFNULL around the actual field.

SELECT IFNULL(as_title, 'lorem ipsum') FROM all_scheduled_messages WHERE id = 1 LIMIT 1;

However, if you're returning a blank, rather than a NULL from your query, IFNULL will not work. You'll need to NULL the value first:

SELECT IFNULL(NULLIF(as_title, ''), 'lorem ipsum') FROM all_scheduled_messages WHERE id = 1 LIMIT 1;

Related Solutions

Mysql – How to use SUBSTRING using REGEXP in MySQL

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string. The basic locate syntax you would need is explained here.

LOCATE (search str, str, [position])

search str = A string which will be searched for.

str =A string which is going to be searched.

position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here

SUBSTRING(str, pos, len)

str= A string.

pos= Starting position.

len= Length in characters.

The easier way of viewing this is to think of substring as the following SUBSTRING(str FROM pos FOR len)

The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.

declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)

Mysql – Inserting empty string on Windows MySQL doesn’t use default value

PROBLEM

You need to insert nothing ?

SOLUTION

Then, insert nothing at all ... literally !!!

One of the following will work

INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);

SAMPLE DATA

use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2
    -> (id int not null auto_increment primary key,
    -> test int default 0);
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql>

QUERIES EXECUTED

If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT #1 : I did this in Windows 8.1 from the command line

mysql> select * from information_schema.global_variables
    -> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
| VERSION                 | 5.6.15                       |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)

mysql>

CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.

1

2