sql null's

nelnel Member Posts: 2,859 ■□□□□□□□□□
hi there,

im writing a query which produces a lot of null's on a test mysql db ive got going. i was just wondering (for learning purposes) how you replace the blank null spaces with a word like null or any other word for that matter?
Xbox Live: Bring It On

Bsc (hons) Network Computing - 1st Class
WIP: Msc advanced networking

Comments

  • JDMurrayJDMurray MSIT InfoSec CISSP SSCP GSOM GSEC EnCE C|EH Cloud+ CySA+ CASP+ PenTest+ Security+ Surf City, USAAdmin Posts: 12,675 Admin
    If the data type of the field is a string or varchar, set the default value of the field to the literal string "NULL". The DB NULL value is used to indicate that a field has never been initialized.
  • BeaverC32BeaverC32 Member Posts: 670 ■■■□□□□□□□
    I am assuming that null values are already in the database, and you simply want to alter the output in your SELECT statement. If this is the case, use the COALESCE function.

    Example:

    Select FirstName, LastName, (COALESCE(MiddleName, "No Middle Name")
    FROM Employee

    In this example, COALESCE takes the first argument, "MiddleName", and outputs that value. If there is no value (aka NULL), it outputs the second argument "No Middle Name".
    MCSE 2003, MCSA 2003, LPIC-1, MCP, MCTS: Vista Config, MCTS: SQL Server 2005, CCNA, A+, Network+, Server+, Security+, Linux+, BSCS (Information Systems)
  • nelnel Member Posts: 2,859 ■□□□□□□□□□
    ive tried that coalesce and it doesnt seem to work, the output has multiple fields which have null values and simply have nothing written in the box.
    Does coalesce work on all dbms's?
    Xbox Live: Bring It On

    Bsc (hons) Network Computing - 1st Class
    WIP: Msc advanced networking
  • JDMurrayJDMurray MSIT InfoSec CISSP SSCP GSOM GSEC EnCE C|EH Cloud+ CySA+ CASP+ PenTest+ Security+ Surf City, USAAdmin Posts: 12,675 Admin
    nel wrote:
    Does coalesce work on all dbms's?
    Not all DBMS support COALESCE, but it looks like MySQL supports the COALESCE function: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

    You can try using ISNULL in a case statement as an alternative.
  • BeaverC32BeaverC32 Member Posts: 670 ■■■□□□□□□□
    Whoops, sorry! I didn't read your post carefully enough; I'm only familiar with SQL Server, not mySQL. I would double-check the syntax to make sure the format I originally gave in the example translates over to mySQL.
    MCSE 2003, MCSA 2003, LPIC-1, MCP, MCTS: Vista Config, MCTS: SQL Server 2005, CCNA, A+, Network+, Server+, Security+, Linux+, BSCS (Information Systems)
  • blargoeblargoe Self-Described Huguenot NC, USAMember Posts: 4,174 ■■■■■■■■■□
    I haven't messed with queries in a long time, but isn't it just a simple update statement? Like

    UPDATE Customers SET Address = 'textstring" where Address is NULL
    IT guy since 12/00

    Recent: 11/2019 - RHCSA (RHEL 7); 2/2019 - Updated VCP to 6.5 (just a few days before VMware discontinued the re-cert policy...)
    Working on: RHCE/Ansible
    Future: Probably continued Red Hat Immersion, Possibly VCAP Design, or maybe a completely different path. Depends on job demands...
Sign In or Register to comment.