This is a very specific problem and solution, so it’s helpfulness is questionable. It very likely makes bad data even worse. However, at times the things that are right and just in this world are not what needs to come out the other side of the machine at the end of the day.

While working in a SQL Server sproc that generates data for a client, I needed to:

Ensure this variable length nvarchar value has at least 4 trailing zeros. The given values had their 0s chopped off at times. And sometimes it’s not all of them.

(Dear God, why?)

Because the product codes are variable length, I can’t rely on the standard “pad this string until it is length 10” idea.

Here’s how I’m working through it right now.

Breaking it down

First things first… Where are we?

Using a combination of REVERSE() and PATINDEX() with a negating wildcard [^0] I am able to ask “How many characters from the end is the first instance of not 0?”

GOOD:
  Start:      '1012340000'
  REVERSE():  '0000432101'
               |||||
               12345 --> Index of first non '0': 5

BAD:
  Start:      '10123400'
  REVERSE():  '00432101'
               |||
               123 --> Index of first non '0': 3!

DECLARE @mpc NVARCHAR(20) = '10123400'

SELECT @mpc [mpc],
  PATINDEX('%[^0]%', REVERSE(@mpc)) as chars_from_end

/*******  **************
mpc       chars_from_end
10123400  3
*/

Add some scribbles

Now that we know how many characters from the end to the first non-zero, we can calculate how many zeros we need to append. Finally we can add these using the REPLICATE() function. I expect this index to be 5, so anything less needs some +'0' loves.

BAD:
  Start:      '10123400'
  REVERSE():  '00432101'
  [index]:       3
  [expected]:      5
  ZerosToAdd: [expected] - [index] = 2
                                     |
        CONCAT( @mpc, REPLICATE('0', 2 ) )

  End:        '1012340000'

DECLARE @mpc NVARCHAR(20) = '10123400'

SELECT @mpc                                                             as [mpc],
  PATINDEX('%[^0]%', REVERSE(@mpc))                                     as [chars_from_end],
  5 - PATINDEX('%[^0]%', REVERSE(@mpc))                                 as [zeros_to_add],
  CONCAT( @mpc, REPLICATE('0', 5 - PATINDEX('%[^0]%', REVERSE(@mpc))) ) as [result]

/******** **************  ************  **********
mpc       chars_from_end  zeros_to_add  result
10123400  3               2             1012340000
*/

Thanks to CONCAT(), NULLS are gracefully handled for us in the event that there are a billionty 0s already on the end.

Simplify

This can be summarized into a function for cleaner usage later:

-- Ensure a string ends with at least X number of a char.
-- @examples:
--     SELECT fn_pad_trailing_chars('12345', 4, '0')
--     => '123450000'
--
--     SELECT fn_pad_trailing_chars('12345000', 4, '0')
--     => '123450000'
--
--     SELECT fn_pad_trailing_chars('123450000', 4, '0')
--     => '123450000'

CREATE FUNCTION dbo.fn_pad_trailing_chars(@mpc NVARCHAR(100), @ensure_this_many INT, @char NVARCHAR(1))

RETURNS NVARCHAR(100)
BEGIN
  RETURN CONCAT(
    @mpc,
    REPLICATE(
      @char,
      (
        1 + @ensure_this_many                         -- Expected
        -                                             -- -
        PATINDEX('%[^' + @CHAR + ']%', REVERSE(@mpc)) -- Actual
      )
    )
  )
END

GO

DECLARE @mpc NVARCHAR(20) = '10123400'
SELECT @mpc [mpc], fn_pad_trailing_chars(@mpc, 4, '0') [val]

/*******  **********
mpc       val
10123400  1012340000
*/

For Pete’s Sake, (Probably) Don’t Do This.

This IS foolish and dangerous to just append 0s to the codes all willy nilly. What if the code is supposed to be ‘1234500000’ but is written as ‘123450’? This is taking bad data, and potentially making it worse. For now, I just needed to pad some zeros with a shotgun to help a guy out downstream while reviewing some data. A better solution is to drop all trailing 0s for comparison, and take records that only have a single match. But that’s not what today calls for.

Please don’t add random amounts of 0 to your data. Also, if you’re a human doing data entry, don’t drop them willy nilly before saving it to your db, and we don’t have to have this chat in the first place.