Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 LTRIM & RTRIM not working properly
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aoriju
Posting Yak Master

India
156 Posts

Posted - 04/19/2013 :  02:04:47  Show Profile  Reply with Quote
DECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));
INSERT INTO @tbl_a
SELECT 'KOLATHUR' ,'332113 '
INSERT INTO @tbl_a
SELECT 'NELLAYI' , '332113  '
INSERT INTO @tbl_a
SELECT 'KODAKARA' , '332113  '
INSERT INTO @tbl_a
SELECT 'CHALAKUDY' , '332113'

SELECT * FROM @tbl_a WHERE DIscode = LTRIM(LTRIM('332113 '))


not getting all records

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 04/19/2013 :  02:12:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which collation are you using?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

aoriju
Posting Yak Master

India
156 Posts

Posted - 04/19/2013 :  02:20:19  Show Profile  Reply with Quote
SQL_Latin1_General_CP1_CI_AS
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/19/2013 :  02:20:32  Show Profile  Reply with Quote
shouldnt it be this?



SELECT * FROM @tbl_a WHERE RTRIM(LTRIM(DIscode)) = '332113'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

aoriju
Posting Yak Master

India
156 Posts

Posted - 04/19/2013 :  02:23:03  Show Profile  Reply with Quote
Visakh, that will retieve only 1 record...i need all
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/19/2013 :  02:38:06  Show Profile  Reply with Quote
nope it will retrieve all
see this illustration
Please note that there's no space at the end of the constant value

DECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));
INSERT INTO @tbl_a
SELECT 'KOLATHUR' ,'332113 '
INSERT INTO @tbl_a
SELECT 'NELLAYI' , '332113  '
INSERT INTO @tbl_a
SELECT 'KODAKARA' , '332113  '
INSERT INTO @tbl_a
SELECT 'CHALAKUDY' , '332113'

SELECT * FROM @tbl_a WHERE LTRIM(RTRIM(DIscode)) = '332113'


output
-----------------------------------------
place	        DIscode
--------------------------
KOLATHUR	332113 
NELLAYI	        332113  
KODAKARA	332113  
CHALAKUDY	332113



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 04/19/2013 02:39:56
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/19/2013 :  02:40:51  Show Profile  Reply with Quote
my other guess is it may be a hardspace (char(160)) in which case solution should be

SELECT * FROM @tbl_a WHERE REPLACE(DIscode,CHAR(160),'') = '332113'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/19/2013 :  12:33:16  Show Profile  Reply with Quote
SQL ignores traiing spaces (in most situations), so something else is going on. As Visakh mentions, it might be a "funky" character.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/19/2013 :  14:12:48  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by aoriju

DECLARE @tbl_a AS TABLE(place VARCHAR(100), DIscode VARCHAR(100));
INSERT INTO @tbl_a
SELECT 'KOLATHUR' ,'332113 '
INSERT INTO @tbl_a
SELECT 'NELLAYI' , '332113  '
INSERT INTO @tbl_a
SELECT 'KODAKARA' , '332113  '
INSERT INTO @tbl_a
SELECT 'CHALAKUDY' , '332113'

SELECT * FROM @tbl_a WHERE DIscode = LTRIM(LTRIM('332113 '))


not getting all records



Not that this is your issue, but you are doing LTRIM...LTRIM, no RTRIM.

I cut and paste your code, and I get all records, even with no rtrim.

-Chad
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000