Re: Functions and NULL with standard install

[Shawn Green]

Hello Lucas,

[EMAIL PROTECTED] wrote:

I don't see how  to use this here, I will have to research the |/||/|

select rpad(|IFNULL(|null, ''),5,'1');

|/||/|
snip
  


The function you want to use is IFNULL() documented 
here:http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html


Try it as he wrote it just without the pipes (|):

 SELECT RPAD(IFNULL(column_name, ''),5,'1');

Another option to the IFNULL  and CASE is the COALESCE function: 
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html


--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN

 Join us at the 2007 MySQL Conference and Exposition
 April 23 to 26 - Santa Clara, California, USA
 http://www.mysqlconf.com/




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Functions and NULL with standard install

[Joshua Marsh]

On 3/21/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:


I am running a basic install of MySQL 5.0 with strict mode turned on  I
would like to use RPAD however at times a NULL var will be sent to the
function. My goal is to have a function that will convert the NULL to a
blank string to get the result of EXAMPLE3 if a NULL is returned.


EXAMPLE 1

mysql select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)



Try this:

SELECT RPAD(CASE WHEN NULL IS NULL THEN '' ELSE '2' END, 5, '1');

If you are using it on an actual field, you would replace the first NULL and
the '2' with the field to be checked.

Re: Functions and NULL with standard install

[Francesco Riosa]

select rpad(|IFNULL(|null, ''),5,'1');

|/||/|
[EMAIL PROTECTED] ha scritto:
I am running a basic install of MySQL 5.0 with strict mode turned on  I 
would like to use RPAD however at times a NULL var will be sent to the 
function. My goal is to have a function that will convert the NULL to a 
blank string to get the result of EXAMPLE3 if a NULL is returned.



EXAMPLE 1

mysql select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)

EXAMPLE 2
mysql select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0   |
+-+
1 row in set (0.00 sec)

mysql

EXAMPLE 3

mysql select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1  |
++
1 row in set (0.02 sec)


Is there a function I can use to convert the null string to a blank string 
before it is sent to RPAD in a basic installation of MySQL or will I need 
to create a function?



Wishing you the best you know you deserve,

__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone 609.485.5401
  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Functions and NULL with standard install

[Lucas . CTR . Heuman]

I don't see how  to use this here, I will have to research the |/||/|

select rpad(|IFNULL(|null, ''),5,'1');

|/||/|


but this below works thank you 

SELECT RPAD(CASE WHEN NULL IS NULL THEN '' ELSE '2' END, 5, '1');

If you are using it on an actual field, you would replace the first NULL 
and
the '2' with the field to be checked.



On 3/21/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

 I am running a basic install of MySQL 5.0 with strict mode turned on I
 would like to use RPAD however at times a NULL var will be sent to the
 function. My goal is to have a function that will convert the NULL to a
 blank string to get the result of EXAMPLE3 if a NULL is returned.


 EXAMPLE 1

 mysql select rpad(null,5,'1');
 +--+
 | rpad(null,5,'1') |
 +--+
 | NULL |
 +--+
 1 row in set (0.00 sec)


Try this: