On Thu, May 23, 2013 at 2:06 PM, Jill Rabinowitz
<[email protected]>wrote:
> Hello,
>
> I am having trouble with a SQLite IFNULL and replace statements. I am
> trying to put a value into a column that has no value / has a zero length.
> I am wondering whether anyone can shed light on this.
>
> I'm trying to set column firstname to 'xxx' if the column has a NULL value
> (or has length = 0). I am able to check the number of rows returned by
> running a select statement with the following "where" clauses:
> 1) where firstname IS NULL -------------> 0 rows returned
> 2) where length(firstname) = 0 -------------> returns 100 rows
> 3) where firstname="" --------------> returns 100 rows
>
it appears that your "firstname" columns have been loaded with empty
strings rather than NULLs. You seem to already understand the difference
there, so I won't explain....
>
> The problem is that the IFNULL and REPLACE functions are not working in my
> SELECT statement, so I am unable to set firstname = 'xxx' where no value
> exists.
>
Maybe this:
SELECT ifnull(nullif(firstname,''),'xxx') ...;
Or this:
SELECT CASE WHEN firstname IS NULL OR firstname='' THEN 'xxx' ELSE
firstname END, ...
>
> 1) select IFNULL(firstname, 'xxx') <--------------- does
> not set the value to 'xxx', which is consistent with (1) above
> from tablename;
>
> 2) select replace(firstname, '','xxx') <--------------- does
> not set the value to 'xxx', which contradicts (3) above, as the string is
> empty
>
> Does anyone know how I can check the column and set it to a value if it has
> nothing in it?
>
> Thank you in advance!
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
