A solution to ensure a string ends with X number '0's in SQL Server.
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 0
s 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.