How do delete multiple CHAR(9) at end of string

  • I have a situation where at the end of a remarks string, there is anywhere from 1 -9 "char(9)" at the end of the string.

    Is there any tried and true function or code that can easily without looping, get rid of those unwanted characters and clean up my remarks code???

    HELP....and thank you!!! :w00t:


    Thank you!!,

    Angelindiego

  • Do you only have those characters at the end of your string? you could simply use REPLACE or if you just want to eliminate the last ones, there's a more complex approach.

    Here are some examples:

    DECLARE @String char(9), @Final varchar(9)

    SELECT @String = 'asdf'

    SELECT @Final = @String

    PRINT @Final + @String

    SELECT @Final = LEFT( @String, 10/*Length + 1*/ - PATINDEX( '%[^]%', REVERSE(@String)))

    PRINT @Final + @String

    SELECT @Final = REPLACE(@String, CHAR(9), '')

    PRINT @Final + @String

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you....gives me something to go on!! I will give this a shot!


    Thank you!!,

    Angelindiego

  • turns out that there are spaces and tabs at the beginning and end of the file. Issue is to strip out all of this and just leave what is valid.


    Thank you!!,

    Angelindiego

  • Could you post some sample data in consumable format(Insert statements or a CTE)? That would help us to know what would be the best option.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • If you look at the fourth article in my signature links, therein lies a tool that I think is the Swiss army knife of all string handling functions. It is called PatternSplitCM and it can be applied to your situation by splitting the string on the offending characters, setting the first and last rows returned to an empty string, then reforming the strings using FOR XML PATH[/url].

    DECLARE @Garbage CHAR(2) = ' ' + CHAR(9);

    WITH SampleData (MyString) AS

    (

    SELECT 'Here''s a remark with trailing garbage' + ' ' + REPLICATE(CHAR(9),3)

    UNION ALL SELECT ' ' + REPLICATE(CHAR(9),3) + 'Here''s a remark with leading garbage'

    UNION ALL SELECT 'Here''s a remark with no garbage'

    ),

    SplitOnGarbageChars AS

    (

    SELECT *

    ,rn=ROW_NUMBER() OVER (PARTITION BY MyString ORDER BY ItemNumber DESC)

    FROM SampleData

    CROSS APPLY dbo.PatternSplitCM(MyString, '[' + @Garbage + ']')

    )

    SELECT MyString

    ,CleanedString=

    ((

    SELECT CASE WHEN 1 IN (ItemNumber, rn) AND [Matched] = 1 THEN '' ELSE Item END

    FROM SplitOnGarbageChars b

    WHERE a.MyString = b.MyString

    ORDER BY ItemNumber

    FOR XML PATH(''), TYPE).value('.','varchar(max)')

    )

    FROM SplitOnGarbageChars a

    GROUP BY MyString;

    There may be faster approaches directed specifically at what you need to do, but this will work.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • The CROSS APPLY and SUBSTRING below should do what you need. Let us know if you need some clarification for getting it into an UPDATE statement.

    DECLARE @leading_trailing_chars_to_eliminate varchar(10)

    SET @leading_trailing_chars_to_eliminate = CHAR(9) + SPACE(1)

    ----------------------------------------------------------------------------------------------------

    DECLARE @patindex_pattern varchar(20)

    SET @patindex_pattern = '%[^' + @leading_trailing_chars_to_eliminate + ']%'

    SELECT

    '~~' + remarks + '~~' AS [~~remarks_original~~],

    '~~' + SUBSTRING(remarks, starting_byte, LEN(remarks) - starting_byte - ending_byte + 2) + '~~' AS [~~remarks_updated~~]

    FROM (

    SELECT CHAR(9)+CHAR(9)+SPACE(3)+CHAR(9) + 'remarks themselves' + CHAR(9)+SPACE(2)+CHAR(9)+SPACE(1)+CHAR(9) AS remarks

    ) AS test_data

    CROSS APPLY (

    SELECT

    --find the first not-to-be-eliminated character in the string

    PATINDEX(@patindex_pattern, remarks) AS starting_byte,

    --find the last not-to-be-eliminated character in the string

    PATINDEX(@patindex_pattern, REVERSE(remarks)) AS ending_byte

    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Angelindiego (10/10/2013)


    turns out that there are spaces and tabs at the beginning and end of the file. Issue is to strip out all of this and just leave what is valid.

    If there are no tabs embedded in the useful data, just replace all occurances of CHAR(9) with nothing and then do an LTRIM/RTRIM to get rid of leading and trailing spaces.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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