How to use replace function

  • mcfarlandparkway


    Points: 7642

    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: 244426

    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.
    See for details of how to post T-SQL code-related questions.

  • MVDBA (Mike Vessey)


    Points: 21757

    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


  • ScottPletcher

    SSC Guru

    Points: 98426

    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