How to use replace function

  • mcfarlandparkway

    SSCertifiable

    Points: 7598

    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?

     

  • Phil Parkin

    SSC Guru

    Points: 243772

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21020

    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

  • ScottPletcher

    SSC Guru

    Points: 98174

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply