#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    7
    Rep Power
    0

    Why can I use a "replace" field in select?


    This works.

    Code:
    mysql> select store_num, store_code, postal_zip, replace(postal_zip, ' ', '') postal_zip_nospace from Stores where store_code = 'SMARTMART';
    +-----------+------------+------------+--------------------+
    | store_num | store_code | postal_zip | postal_zip_nospace |
    +-----------+------------+------------+--------------------+
    |       360 | SMARTMART  | N3S 3P4    | N3S3P4             |
    +-----------+------------+------------+--------------------+
    1 row in set (0.00 sec)
    But I can't then use the postal_zip_nospace field at the end there now?
    Code:
    mysql> select store_num, store_code, postal_zip, replace(postal_zip, ' ', '') postal_zip_nospace from Stores where store_code = 'SMARTMART' and postal_zip_nospace = 'whatever';
    ERROR 1054 (42S22): Unknown column 'postal_zip_nospace' in 'where clause'
    What gives?
  2. #2
  3. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,271
    Rep Power
    4193
    The fields listed in SELECT define what you want in your results and are processed last. The conditions listed in your WHERE clause are processed before that so you have to use your actual field names, not aliases.

    If you want to apply a function, then you have to do that in both the WHERE and the SELECT.
    Code:
    select store_num, store_code, postal_zip, replace(postal_zip, ' ', '') postal_zip_nospace from Stores 
    where store_code = 'SMARTMART' and replace(postal_zip, ' ', '') = 'whatever';
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    i know y'all are going to say whoa, that's extra coding, but with a good text or sql editor, the effort is trivial

    by the way, it's called a column alias

    you had this --
    Code:
    SELECT store_num
         , store_code
         , postal_zip
         , REPLACE(postal_zip, ' ', '') postal_zip_nospace 
      FROM Stores 
     WHERE store_code = 'SMARTMART' 
       AND postal_zip_nospace = 'whatever';  /* error */
    remember push and pop stacks from computer courses?

    just push the query down one level into a subquery, and then you can use the column alias in your outer query WHERE clause

    if the expression underneath the column alias is very complex, this method is simpler than re-writing the complex expression in your single query WHERE clause

    Code:
    SELECT *  
      FROM ( SELECT store_num
                  , store_code
                  , postal_zip
                  , REPLACE(postal_zip, ' ', '') postal_zip_nospace 
               FROM Stores 
              WHERE store_code = 'SMARTMART'
           ) q1
     WHERE postal_zip_nospace = 'whatever'
    note "select star" in this case is perfect -- simple and elegant

    usually, "select star" is evil, but in this case you can see which columns are involved, they're right there in the sql, in particular the column that's getting an alias
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    If the only purpose of "postal_zip_nospace" is to match the "whatever" value for the search then is no need for it be in the field list at all.
    Code:
    select store_num
         , store_code
         , postal_zip
    from Stores 
    where store_code = 'SMARTMART' 
          and replace(postal_zip, ' ', '') = 'whatever';
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    7
    Rep Power
    0
    Originally Posted by Barand
    If the only purpose of "postal_zip_nospace" is to match the "whatever" value for the search then is no need for it be in the field list at all.
    Code:
    select store_num
         , store_code
         , postal_zip
    from Stores 
    where store_code = 'SMARTMART' 
          and replace(postal_zip, ' ', '') = 'whatever';
    Thanks, I like this the most. do the replace after not before.
  10. #6
  11. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,677
    Rep Power
    1841
    Maybe only a small point, but having a data modification function (here it is the REPLACE()) in the WHERE clause will prevent any use of an index on that modified column.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by SimonJM
    Maybe only a small point...
    but a good one

    note this also applies in the version i posted, where the function is in the subquery and the WHERE condition is in the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo