Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Ltrim rtrim no working with UPDATE Expand / Collapse
Author
Message
Posted Monday, February 23, 2009 5:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
Hi Friends,

I am facing an Issue while writing an update statement. The statement is like below

update tablename
set colname = ltrim(rtrim(colname)
where filter condition

the colname is of type CHAR.

trying to clean up the data and cant change the data type of the column.

Please advice.

Regards,
Sriram


Sriram

Post #662486
Posted Monday, February 23, 2009 5:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 12,880, Visits: 31,796
if you declare a char(50), and put a single character in it, because of it's datatype it will add 49 spaces on the end.
that's the expected behavior.
Varchar(50), on the other hand, will behave as you expect...trim it, and it will not contain spaces at the end.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #662488
Posted Monday, February 23, 2009 5:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
Lowell,

Thats the expected behavoiur fine, but the point here is, maybe for example I can put it like below. The column is of length 5. and I have more than 10 k records in that.

the result that i get for few records when i do a copy paste is

'WDT'
' WDT '

Regards,
Sriram


Sriram

Post #662489
Posted Monday, February 23, 2009 6:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
Are you sure that 1st character is a normal space (character code 32)?

declare @s1 char(10), @s2 char(10)
select @s1 = CHAR(160) + 'WDT' + CHAR(160)
select @s1, '[' + LTRIM(RTRIM(@s1)) + ']'

select @s2 = CHAR(32) + 'WDT' + CHAR(32)
select @s2, '[' + LTRIM(RTRIM(@s2)) + ']'

Post #662496
Posted Monday, February 23, 2009 6:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
the problem that I am facing with this is application is using colname= 'WDT' and I have 10 records against that value out of which 9 were inserted earlier and 1 has been recently inserted. now I am getting only 9 records in the app instead of 10 which is creating an issue. and cannot do a code change in the app. so trying to update the data, all in vein

Sriram

Post #662505
Posted Monday, February 23, 2009 6:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
tried to directly update with 'WDT' even that is not wokring for that records. running out of ideas.

Sriram

Post #662513
Posted Monday, February 23, 2009 6:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
That's why Andrew said to check and make sure that what look like spaces, actually are... it's a data problem... the trim functions will not remove all white-space characters... just spaces.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662515
Posted Monday, February 23, 2009 6:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
What do you get if you run this (after changing columns and table names appropriately)?

SELECT DISTINCT 
[Code] = ASCII(SUBSTRING(LTRIM(columnName), 1, 1)),
[Character] = '[' + SUBSTRING(LTRIM(columnName), 1, 1) + ']'
FROM tableName
WHERE (columnName LIKE '%WDT%')

Post #662520
Posted Monday, February 23, 2009 6:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
I get he below sol.

87 [W]


Sriram

Post #662523
Posted Monday, February 23, 2009 6:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 7,048, Visits: 6,797
I would do the following to find the offending values

SELECT colname,CAST(colname as varbinary(5)) 
FROM tablename
WHERE colname LIKE '%WDT'
AND colname <> 'WDT'




Far away is close at hand in the images of elsewhere.

Anon.

Post #662524
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse