November 8, 2019 at 2:51 pm
I have an ID column data that is having space at the last, and i am comparing this column data with other table column data as its nto matched getting NULLS
ID - 65447093
if i copy and paste in notepad, the above id has space at the last and hence it accepting 9 charactrers; tried ltrim rtrim but didn't worked.
rtrim(ltrim(ab.ID)) = (CASE WHEN LEN(rtrim(ltrim(ab.ID)))=8 THEN RIGHT(en.IDTrim,8) ELSE en.IDTrim END)
how replace func will work here?
November 8, 2019 at 3:14 pm
What is the datatype of the ID column?
As an aside, calling columns 'ID' is rather bad practice, as it provides no hint as to what sort of ID it is.
Being extra picky, 'ID' stands for Identity Document. If you mean 'Identifier', Id is more accurate.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 8, 2019 at 3:36 pm
sounds like you might have some odd characters at the end of your field - they look like spaces in notepad, but might be CR or LF (or other invisible characters) - I've had a few instances where LTRIM(RTRIM didn't fix this.
most of the time this has come from some sort of data import or copy/paste incident.
I've never found a really good way of dealing with this other than deleting the text and re-keying it manually
MVDBA
November 8, 2019 at 4:18 pm
Be aware, too, that an int column could require up to 10 bytes to store its value. It's not safe to limit your result from this column to just 8 chars.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply