With mssql I'd suggest to write an according function, which first uses LTRIM to remove the leading (and only the leading) zeros. And then uses REPLICATE to create the respective number of underscores.

CREATE FUNCTION replacezero
(
    @s VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @trimmed VARCHAR(MAX) = LTRIM(@s, '0')
    RETURN REPLICATE('_', LEN(@s)- LEN(@trimmed)) + @trimmed
END
GO

You can also do without the extra @trimmed but then you would need to execute LTRIM twice

Which you then can use as

SELECT dbo.replacezero(mycol) FROM my table

use try_cast to make it an INT, then use STR to make it a right-aligned string of the appropriate length, then replace spaces with underscores:

declare @mycol varchar(11)
set @mycol = '0001234'
select replace(str(try_cast(@mycol as int),len(@mycol)),' ','_');

right(
    replicate('_', 100) +
        stuff(MyCol, 1, patindex('%[^0]%', MyCol) - 1, ''),
    len(MyCol)
)

https://dbfiddle.uk/-xUtQ_he

This is pure character manipulation. Find the index of the first non-zero character and remove everything before that via stuff() . Then you can use the standard left-padding technique of prepending underscores and keeping the same number of characters as the original string. This should work across all versions of SQL Server. This is a single inline expression, no casting is involved and no function installations are required.

To handle a string of all zeroes you can augment that with something like this:

right(
    replicate('_', 100) +
        rtrim(stuff(MyCol, 1, patindex('%[^0]%', MyCol + ' ') - 1, '')), 
    len(MyCol)
)

Creating a function, as #derpircher suggests , is the ideal solution.

If you can't, because you only have read permission, and must use in a query you could use the following expression:

CONCAT(REPLICATE('_', PATINDEX('%[^0]%', MyCol)-1), TRY_CAST(MyCol AS INT));

The PATINDEX function found the first occurrence of a char different from zero.

Full test code that return __12004 :

DECLARE @value NVARCHAR(MAX) = '0012004';
SELECT CONCAT(REPLICATE('_', PATINDEX('%[^0]%', @value)-1),  TRY_CAST(@value AS INT));

Version with fixed length to 11 and works with no leading zero

DECLARE @value NVARCHAR(MAX) = '00012004';
SELECT 
    CONCAT(REPLICATE('_', 11-LEN(@value)),  REPLICATE('_', PATINDEX('%[^0]%', @value)-1),  TRY_CAST(@value AS INT));