SQL Server Forums
Profile | Register | 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
 New Topic  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
29910 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
52249 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
52249 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
52249 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

4364 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000