How does RTRIM work in this query mentioned below

  • Can someone please help me explain whay inspite of having RTRIM function the below mentioned queries give different results :-- RTEIM trims trainling space ,so the result in both cases should be same.

    USE AdventureWorks;

    GO

    CREATE PROCEDURE FindEmployee @EmpLName char(20)

    AS

    SELECT @EmpLName = RTRIM(@EmpLName) + '%';

    SELECT c.FirstName, c.LastName, a.City

    FROM Person.Contact c JOIN Person.Address a ON c.ContactID = a.AddressID

    WHERE c.LastName LIKE @EmpLName;

    GO

    EXEC FindEmployee @EmpLName = 'Barb';

    GO

    In the FindEmployee procedure, no rows are returned because the char variable (@EmpLName) contains trailing blanks whenever the name contains fewer than 20 characters. Because the LastName column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant.

    However, the following example succeeds because trailing blanks are not added to a varchar variable.

    Copy Code

    USE AdventureWorks;

    GO

    CREATE PROCEDURE FindEmployee @EmpLName varchar(20)

    AS

    SELECT @EmpLName = RTRIM(@EmpLName) + '%';

    SELECT c.FirstName, c.LastName, a.City

    FROM Person.Contact c JOIN Person.Address a ON c.ContactID = a.AddressID

    WHERE c.LastName LIKE @EmpLName;

    GO

    EXEC FindEmployee @EmpLName = 'Barb';

    Here is the result set.

    Copy Code

    FirstName LastName City

    ---------- ----------------------------------------

    Angela Barbariol Snohomish

    David Barber Snohomish

    (2 row(s) affected)

  • I'm not completely sure why the "char" parameter isn't working. The table field "LastName" is nvarchar(50). A "char" can be implicitly converted to an "nvarchar".

    But converting your "char" parameter to an "nvarchar" first will solve your problem:

    CREATE PROCEDURE FindEmployee @EmpLName char(20)

    AS

    DECLARE @LName nvarchar(20)

    SET @LName = RTRIM(@EmpLName) + '%'

    --SELECT @EmpLName = RTRIM(@EmpLName) + '%';

    SELECT c.FirstName, c.LastName, a.City

    FROM Person.Contact c JOIN Person.Address a ON c.ContactID = a.AddressID

    WHERE c.LastName LIKE @LName --@EmpLName;

    GO

    EXEC FindEmployee @EmpLName = 'Barb';

    GO

  • yep when using a varchar for LIKE statemnts, the spaces are significant. a varchar contaning spaces at the end maintains the spaces; it's when you look for equal to a name that the comparison removes the trailing spaces:

    [font="Courier New"]

    DECLARE @table TABLE (FirstName   VARCHAR(30),

                         LastName    VARCHAR(30),

                         City        VARCHAR(30) )

    INSERT INTO @table

      SELECT 'Angela     ','Barbariol    ','Snohomish    ' UNION ALL

      SELECT 'David     ','Barber     ','Snohomish    ' UNION ALL

      SELECT CONVERT(CHAR(15),'Angela     '),CONVERT(CHAR(15),'Barbariol    '),CONVERT(CHAR(15),'Snohomish    ') UNION ALL

      SELECT CONVERT(CHAR(30),'David     '),CONVERT(CHAR(30),'Barber     '),CONVERT(CHAR(30),'Snohomish    ') UNION ALL

      SELECT CONVERT(VARCHAR(15),'Angela     '),CONVERT(VARCHAR(15),'Barbariol    '),CONVERT(VARCHAR(15),'Snohomish    ') UNION ALL

      SELECT CONVERT(VARCHAR(30),'David     '),CONVERT(VARCHAR(30),'Barber     '),CONVERT(VARCHAR(30),'Snohomish    ') UNION ALL

      SELECT RTRIM('Angela     '),RTRIM('Barbariol    '),RTRIM('Snohomish    ') UNION ALL

      SELECT RTRIM('David     '),RTRIM('Barber     '),RTRIM('Snohomish    ')

    SELECT '>' + FirstName + '<' AS FirstName,

    '>' + LastName + '<' AS LastName,

    '>' + City + '<' AS City

    FROM @table

    SELECT * FROM @table WHERE LastName LIKE 'Barb%'

    --fat fingered a trailing space for my LIKE statemnt: no results.

    SELECT * FROM @table WHERE LastName LIKE 'Barb %'

    SELECT * FROM @table WHERE LastName ='Barber'

    SELECT * FROM @table WHERE LastName ='Barber     '[/font]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks guys for you answers.I appreciate that

    , but my question remains unanswered.

    The job of RTRIM is to trim spaces.

    so how does it matter whether we use CHAR or VARCHAR because finally there wont be any spaces.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply