Trim trailing characters from a varchar?

Question

Post reply

Trim trailing characters from a varchar?

tacy.highland

Hall of Fame

Points: 3319
More actions
October 2, 2020 at 12:00 am

#3792398

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!

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

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

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.

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

ScottPletcher SSC Guru Points: 100946 More actions · Answer 2

I think this will do it:

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

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

tacy.highland Hall of Fame Points: 3319 More actions · Answer 3

Wow, guys, thank you so much.

The REPLACE actually worked well in my existing query.

Thank you both for your help with this!

ScottPletcher SSC Guru Points: 100946 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

tacy.highland Hall of Fame Points: 3319 More actions · Answer 5

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