Trim trailing characters from a varchar?

  • Hello SQL friends,

    I have a char(75) column with values like the following:

    052000054000000000000000000000000000000000000000000000000000000000000000000

    052054000000000000000000000000000000000000000000000000000000000000000000000

    054000000000000000000000000000000000000000000000000000000000000000000000000

    050051052054000000000000000000000000000000000000000000000000000000000000000

    I only need to see the 050, 051, 052, 053, 054 values from each row.  So I want to remove all trailing 0's and also if there are any weird extra zeros anywhere else like in the first example above.  (I'd be ok with just removing trailing zeros if I couldn't do that)

    How would I do this?  I suspect something with substring and patindex but don't know how to specify that it's only the zeros occurring after the last non-zero number.  (The assumption here is that the numbers would appear in order, so we shouldn't see 051,054,050, or anything like that).

    Does anyone have ideas?

    Thanks much!

     

  • This will actually pull the  individual values by row so you can use them if you need to.

    See Scott's code (below) if you just want to get rid of most of the zero triplets.

    --===== If the test table already exists, drop it to make reruns in SSMS easier.
    -- This type of thing is NOT needed in a Stored Procedure.
    DROP TABLE IF EXISTS #TestTable
    ;
    GO
    --===== We're just creating test data here. It's not really a part of the solution
    -- although some form of unique row identifier is absolutely critical.
    SELECT RowNum = IDENTITY(INT,1,1)
    ,SomeString = CONVERT(CHAR(75),v.SomeString)
    INTO #TestTable
    FROM (VALUES
    ('052000054000000000000000000000000000000000000000000000000000000000000000000')
    ,('052054000000000000000000000000000000000000000000000000000000000000000000000')
    ,('054000000000000000000000000000000000000000000000000000000000000000000000000')
    ,('050051052054000000000000000000000000000000000000000000000000000000000000000')
    )v(SomeString)
    ;
    --===== Solve the problem using the fnTally "Pseudo Cursor", which you can get from the
    -- article at the first link in my signature line below.
    -- This is hardcoded for a string length of 75 but we could easily change that.
    SELECT tst.RowNum
    ,s.StringPart
    FROM #TestTable tst
    CROSS APPLY dbo.fnTally(0,(75-PATINDEX('%[^0]%',REVERSE(tst.SomeString)))/3) t
    CROSS APPLY (SELECT SUBSTRING(tst.SomeString,1+(t.N*3),3)) s (StringPart)
    WHERE s.StringPart > '000'
    ;

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • I think this will do it:

    SELECT REPLACE(string, '000', '')

    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."

  • Wow, guys, thank you so much.

    The REPLACE actually worked well in my existing query.

    Thank you both for your help with this!

  • Glad it helped, thanks for the feedback!

    If you need to split out the final string, you can use a tally table for that:

    ;WITH
    cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cte_tally100 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
    )
    SELECT query1.string#, ca1.*
    FROM (
    SELECT string#, REPLACE(string, '000', '') AS replaced_string
    FROM /*dbo.table_name*/ ( VALUES
    (1, '052000054000000000000000000000000000000000000000000000000000000000000000000'),
    (2, '052054000000000000000000000000000000000000000000000000000000000000000000000'),
    (3, '054000000000000000000000000000000000000000000000000000000000000000000000000'),
    (4, '050051052054000000000000000000000000000000000000000000000000000000000000000')
    ) AS data(string#, string)
    ) AS query1
    CROSS APPLY (
    SELECT SUBSTRING(replaced_string, t.number, 3) AS value
    FROM cte_tally100 t
    WHERE t.number <= LEN(replaced_string) AND t.number % 3 = 1
    ) AS ca1
    ORDER BY string#

    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."

  • Thanks Scott.

    Actually I transformed the field (called COS) using your code first, then used a CTE to split the values and comma delimit them for the final product:

    	------split and comma delimit the category of service field:
    ;WITH CTE
    AS (SELECT orgUnitId, [COS], n = 1
    FROM #temp
    UNION ALL
    SELECT orgUnitId, [COS], n = n + 1
    FROM cte
    WHERE n < LEN([COS]))

    SELECT DISTINCT a.orgUnitId
    ,STUFF((SELECT ',' + SUBSTRING(b.[COS],b.n,3)
    FROM cte AS b
    WHERE b.orgUnitId = a.orgUnitId AND b.n%3 = 1
    ORDER BY b.orgUnitId, b.n
    FOR XML PATH('')),1,1,'') AS Field
    --INTO #CTE
    FROM CTE as a

    Thanks again to you and Jeff for your help.  You guys are fantastic resources.  Whenever I get stuck on something I know the people on this forum can help.

    ~cheers

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

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