Tag Archives: sql

mysql concat does not like NULL values – use concat_ws instead

This just caused me several moments of pain. Here’s hoping I can remove that for you.

I was needing to update a VARCHAR column by incrementally adding values read from another source. Simplest SQL (I thought) was:

UPDATE table SET field = CONCAT(field, 'value ') WHERE id = ID;

All ran fine. But pretty much all of the fields came back NULL. And to find out you have to actually read the details in the mysql manual. A snip from the official write-up on concat:

CONCAT() returns NULL if any argument is NULL.

There’s the problem. Most/all my starting values were NULL. So concatenating actual values in the field did nothing as the NULL won.

The solution was very simple. Replace the CONCAT() function with CONCAT_WS() where the “ws” bit means “with separator”. Rather than just adding a space after the value in my example, specify the delimiter as the first parameter. This makes the revised SQL command:

UPDATE table SET field = CONCAT_WS(' ', field, 'value ') WHERE id = ID;

It’s a lot more forgiven with NULLs; again snipped from the manual:

  • If the separator is NULL, the result is NULL
  • CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Now it does not matter if there are any NULL values involved. All just works.

Getting the iPhone simulator to recognise sqlite database changes done in Terminal

I’ve been tearing my hair out for an afternoon working with a simple DB application for the iPhone/iPad. Essentially it connects to a sqlite3 database as the source file. But each time I edited that database content in Terminal, nothing was changing in the application when run.

Finally a solution. Need to reset the simulator itself to remove the local copy taken/needed when running a DB application.

Option you need is Reset Contents and Settings… under the iPhone Simulator menu.

Once reset when the application is run it pulls the updated database across as its copy. Phew!

Comparison and Logical Operators: Javascript, SQL, PHP and ASP Compared

Comparison and Logical operators are used for testing data values. Sadly the operators to use are not consistent across languages. This article summarises the most common operators for the key web development languages:

  • Javascript
  • ASP
  • PHP
  • SQL

For SQL we checked both mySQL and MS SQL. All operators listed here should work in both.

Comparison

Operator Javascript ASP PHP SQL
Less than < < < <
Less than or equal to <= <= <= <=
Greater than > > > >
Greater than or equal to >= >= >= >=
Equal to == = == (equal)
=== (identical)
=
Not equal to != <> != (not equal)
<> (not equal)
!== (not identical)
<>

Testing for “identical” in PHP arrived with PHP 4. Two values are identical if they are the same, and the same type. Eg the values “1″ (a string) and 1 (an integer) are equal but not identical.

Logical

Operator Javascript ASP PHP SQL
AND && AND &&
AND
AND
OR || OR ||
OR
OR
NOT ! NOT ! NOT

PHP provides two options for AND and OR with different operator precedences. Refer to the PHP manual: Operator Precedence for details.

useful reference lists

Javascript
comparison and logical operators (w3schools)
ASP
comparison and logical operators (msdn)
PHP
comparison operators (php.net)
logical operators (php.net)
MS SQL
comparison and logical operators (msdn)
MySQL
comparison operators (mysql.com)
logical operators (mysql.com)

Logical and comparison operators for common web languages

Comparison and Logical operators are used for testing data values. Sadly the operators to use are not consistent across languages. This article summarises the most common operators for the key web development languages:

  • Javascript
  • ASP
  • PHP
  • SQL

For SQL we checked both mySQL and MS SQL. All operators listed here should work in both.

Comparison

Operator Javascript ASP PHP SQL
Less than < < < <
Less than or equal to <= <= <= <=
Greater than > > > >
Greater than or equal to >= >= >= >=
Equal to == = == (equal)
=== (identical)
=
Not equal to != <> != (not equal)
<> (not equal)
!== (not identical)
<>

Testing for “identical” in PHP arrived with PHP 4. Two values are identical if they are the same, and the same type. Eg the values “1″ (a string) and 1 (an integer) are equal but not identical.

Logical

Operator Javascript ASP PHP SQL
AND && AND &&
AND
AND
OR || OR ||
OR
OR
NOT ! NOT ! NOT

PHP provides two options for AND and OR with different operator precedences. Refer to the PHP manual: Operator Precedence for details.

useful reference lists

Javascript
comparison and logical operators (w3schools)
ASP
comparison and logical operators (msdn)
PHP
comparison operators (php.net)
logical operators (php.net)
MS SQL
comparison and logical operators (msdn)
MySQL
comparison operators (mysql.com)
logical operators (mysql.com)