right trim

  • hi

    I want to right trim from a certain point

    can I use a rtrim with a substring

    SUBSTRING (TaxLotDescription,1,3)

    or whats the best way to do it

  • ronan.healy (8/27/2014)


    hi

    I want to right trim from a certain point

    can I use a rtrim with a substring

    SUBSTRING (TaxLotDescription,1,3)

    or whats the best way to do it

    Left(Col1,[Number of chars to keep])


  • chars might not be the same length. should have stated that sorry but they will be going right

  • So you want to trim from a certain point, and that point varies from one string to the next? Tough.

    Can you describe the requirement more clearly please? Some examples would help.


  • this is a sample from a column BNY Mellon - 07/01/2014

    so all the columns will always have that date format and I want to text the text after the date

  • ronan.healy (8/27/2014)


    this is a sample from a column BNY Mellon - 07/01/2014

    so all the columns will always have that date format and I want to text the text after the date

    No idea what you mean by 'text the text'.

    Nor can I see anything 'after the date'.

    If you just want the right 10 characters, use the RIGHT function.

    If there are unwanted trailing spaces in your source, use RTRIM() too

    Right(RTrim(Col),10)


  • sorry I ment the text before the date.

    that's the text I want to take

    so its like this now BNY Mellon - 07/01/2014

    and I want it like this BNY Mellon

  • LEFT or SUBSTRING with a starting position of 1 are fine.

    Investigate using the CHARINDEX or PATINDEX functions to find the point at which you want to cut off the string, e.g.

    LEFT(ColumnName, PAT/CHARINDEX(Params))

    SUBSTRING(ColumnName, 1, PAT/CHARINDEX(Params))

    Without knowing the rules you're using to determine where to trim the string it's difficult to be more specific.

    Cheers

  • ronan.healy (8/27/2014)


    sorry I ment the text before the date.

    that's the text I want to take

    so its like this now BNY Mellon - 07/01/2014

    and I want it like this BNY Mellon

    DECLARE @MyString VARCHAR(200) = 'BNY Mellon - 07/01/2014'

    SELECT LEFT(@MyString, LEN(@MyString)-LEN(' - 07/01/2014'))

    SELECT LEFT(@MyString, LEN(@MyString)-13)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 9 posts - 1 through 8 (of 8 total)

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