Search code examples
mysqlconcat-ws

SQL concat_ws syntax error

I tried to run the following concat_ws code but I am getting a syntax error. what is wrong with this sql code? 

select count(policy_number) 
from resp_party 
where c_policy_effective_date = concat_ws('-', policy_effective_date_yyyy, 
      (lpad(policy_effective_date_mm, 2, ""00"")),
      (lpad(policy_effective_date_dd, 2, ""00"")))
Solution

  • The double quotes are the problem. You should undouble them or escape them.

    Depending on what you intended, you could either write :

    where c_policy_effective_date = concat_ws('-', policy_effective_date_yyyy, 
      (lpad(policy_effective_date_mm, 2, '"00"')),
      (lpad(policy_effective_date_dd, 2, '"00"')))

    or, most probably (to left pad with zeroes):

    where c_policy_effective_date = concat_ws('-', policy_effective_date_yyyy, 
      (lpad(policy_effective_date_mm, 2, '0')),
      (lpad(policy_effective_date_dd, 2, '0')))

    Note that it is more standard-compliant if you quote strings with single quotes. As from the docs:

    If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier.

    If you intended to escape the double quote, then please note that the way to escape characters is not by doubling them, but by prefixing them with backslash. However, double quotes don't need escaping if you use single quotes for delimiting your string.