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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply