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') |
+-----------------+
| 01111 |
+-----------------+
1 row in set (0.00 sec)

mysql>

EXAMPLE 3

mysql> select rpad('',5,'1');
+----------------+
| rpad('',5,'1') |
+----------------+
| 11111 |
+----------------+
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

On 3/21/07, Lucas.CTR.Heuman@faa.gov <Lucas.CTR.Heuman@faa.gov> 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.

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

|/||/|
Lucas.CTR.Heuman@faa.gov 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') |
> +-----------------+
> | 01111 |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql>
>
> EXAMPLE 3
>
> mysql> select rpad('',5,'1');
> +----------------+
> | rpad('',5,'1') |
> +----------------+
> | 11111 |
> +----------------+
> 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
>

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, Lucas.CTR.Heuman@faa.gov <Lucas.CTR.Heuman@faa.gov> 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:

Hello Lucas,

Lucas.CTR.Heuman@faa.gov 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/...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/...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/