CODEWITHSUNDEEP
mysqlsqlselect
Dan Nissenbaum
Dan Nissenbaum

Reputation: 13918

Can MySQL string functions (such as "TRIM") act on a 1-column SELECT clause?

I have a complex SQL query. My question can be simplified to the query shown here, which gives a syntax error:

SELECT 
  LENGTH 
  (SELECT 
    p.item_description 
  FROM
    ssilogic_main.`products` p 
  WHERE p.item_id = '3M.10118')

The error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT p.item_description FROM ssilogic_main.products p WHERE p' at line 3

Note that the following, similar, SQL query succeeds without error:

SELECT 
  LENGTH 
  ('3M.10118')

... properly returning 8.

The question is: Is it possible to have the MySQL string functions, such as LENGTH or TRIM, act on the 1-column result of a SELECT clause?

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You might be able to write this as:

SELECT LENGTH((SELECT p.item_description 
               FROM ssilogic_main.`products` p 
               WHERE p.item_id = '3M.10118'
              )
             )

Subqueries need to be surrounded by their own set of parentheses. The more natural way to write it is:

SELECT (SELECT length(p.item_description)
        FROM ssilogic_main.`products` p 
        WHERE p.item_id = '3M.10118'
        LIMIT 1
       )

The limit ensures that, indeed, only one row is returned.

Upvotes: 1

Related Questions