Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How does RTRIM work in this query mentioned below


How does RTRIM work in this query mentioned below

Author
Message
anup.sharmaformail
anup.sharmaformail
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
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)
pss
pss
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 158
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 38966
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:


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     '





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!

anup.sharmaformail
anup.sharmaformail
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search