String Conversion: Preserving Leading Zero for Hashing

Question

String Conversion: Preserving Leading Zero for Hashing

Janice Chi 620

We build an MD5 hash by concatenating multiple column values across systems (DB2 for iSeries and Azure SQL). Some source columns are DECIMAL/NUMERIC. When converting decimals to string in DB2 using CHAR/VARCHAR, values like 0.0123 sometimes become .0123 (leading zero is dropped). This causes hash mismatches between DB2 and Azure SQL.

Could you please advise:

The recommended, generic way in DB2 for i (IBM iSeries) SQL to convert DECIMAL/NUMERIC to string so that a leading 0 before the decimal point is always preserved (e.g., .0123 → 0.0123 and -.045 → -0.045), without hardcoding scale/precision?

Any best practices to ensure consistent normalization of decimals to strings for hashing across DB2 and Azure SQL?

Mahesh Kurva 7,570 Reputation points Microsoft External Staff Moderator

2025-08-21T17:32:35.6333333+00:00

Hi Janice Chi ,

I kindly request some time to investigate this further, as I am currently checking internally regarding the behavior of this resource.

I will get back to you soon with an update.

Thank you.
Erland Sommarskog 124.9K Reputation points MVP Volunteer Moderator

2025-08-21T21:25:42.23+00:00
I think you better ask this in a forum devoted to DB2.

If you want to resolve this on the Azure side, you could do:

regexp_replace(cast(decimalcol) as varchar, '^0\.', '.')

to strip the leading zero.

Note: The regexp_replace function is currently in preview in Azure SQL Database.
Oury Ba-MSFT 21,111 Reputation points Microsoft Employee Moderator

2025-08-25T17:44:33.74+00:00

@Janice Chi I wanted to follow up on the above comment. Please let me know if you if you need some additional clarification.

1 answer

Your answer

Mahesh Kurva 7,570 Reputation points Microsoft External Staff Moderator

2025-08-21T17:32:35.6333333+00:00

Hi Janice Chi ,

I kindly request some time to investigate this further, as I am currently checking internally regarding the behavior of this resource.

I will get back to you soon with an update.

Thank you.
Erland Sommarskog 124.9K Reputation points MVP Volunteer Moderator

2025-08-21T21:25:42.23+00:00

I think you better ask this in a forum devoted to DB2.

If you want to resolve this on the Azure side, you could do:

regexp_replace(cast(decimalcol) as varchar, '^0\.', '.')

to strip the leading zero.

Note: The regexp_replace function is currently in preview in Azure SQL Database.
Oury Ba-MSFT 21,111 Reputation points Microsoft Employee Moderator

2025-08-25T17:44:33.74+00:00

@Janice Chi I wanted to follow up on the above comment. Please let me know if you if you need some additional clarification.

Answer 1

@Janice Chi If you are looking for guidance on formatting decimals in Azure SQL to preserve leading zeros. You can use the FORMAT syntax. Check the examples below and let us know if that help in anyways.DB2

Use VARCHAR_FORMAT() with a numeric format string:

sql

-- Preserves leading zero
SELECT VARCHAR_FORMAT(DECIMAL(0.0123, 10, 4), '0.############################') AS formatted
-- Output: '0.0123'

SELECT VARCHAR_FORMAT(DECIMAL(-0.045, 10, 3), '0.############################') AS formatted
-- Output: '-0.045'

This ensures:

Leading zero before decimal

No trailing zeros

Works for both positive and negative values

Azure SQL

Use FORMAT() with a matching format string:

sql

-- Preserves leading zero
SELECT FORMAT(CAST(0.0123 AS DECIMAL(10,4)), '0.############################') AS formatted
-- Output: '0.0123'

SELECT FORMAT(CAST(-0.045 AS DECIMAL(10,3)), '0.############################') AS formatted
-- Output: '-0.045'

Alternatively, for fixed precision:

sql

SELECT FORMAT(CAST(0.0123 AS DECIMAL(10,4)), '0.0000') AS formatted
-- Output: '0.0123'

Share via

String Conversion: Preserving Leading Zero for Hashing

1 answer

Your answer