Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Unanswered: SELECT IF Statament

    Is this a bug? I am writing a report where I don't want a record to span more than 1 line.

    This only happens if the person's name is too long so I thought something like this might work, but it doesn't. Am I doing something wrong or is this a bug?

    SELECT e.EmployeeNo,
    IF(CHAR_LENGTH(CONCAT (e.Salutation,' ',e.Forename,' ',e.Surname)<20),
    CONCAT(e.Salutation,' ',e.Forename,' ',e.Surname),
    CONCAT(e.Salutation,' ',SUBSTRING (e.Forename,1,1),' ',e.Surname)) AS 'Name',
    FROM Employees AS e
    ORDER By EmployeeNo

    Please help!

    No matter how long the person's name is it always plumps for the: Mr. S. Biglongsurname as opposed to Mr Someone Biglongsurname.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have improperly nested parentheses

    IF( CHAR_LENGTH(CONCAT(x)<20)
    , CONCAT(x)
    , CONCAT(y)
    ) AS 'Name'

    CONCAT(x)<20 is either 1 or 0, depending on whether the concatenated string is less than the string '20' or not

    thus CHAR_LENGTH(CONCAT(x)<20) is the CHAR_LENGTH of 1 or 0, and that's always 1

    thus CONCAT(x) is always selected




    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •