By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @TABLE TABLE (ColumnName VARCHAR(512))
INSERT INTO @TABLE
VALUES ('00001A')
,('0000ABCDEFGHI00J000KLMNIOPQSR')
,('00001A01A65463546354654006543606566565')
,('')
,(NULL)
,('A')
,('0')
,('0A')
,('0000000000000000000')
select ColumnName SourceData
,substring(ColumnName, patindex('%[^0]%',ColumnName), 10) [SUBSTRING10_TRICK]
,ISNULL(STUFF(ColumnName,1,patindex('%[^0]%',ColumnName)-1,''),REPLACE(ColumnName,'0','')) [STUFF_TRICK]
FROM @TABLE
| SourceData | SUBSTRING10_TRICK | STUFF_TRICK |
|---|---|---|
| 00001A | 1A | 1A |
| 0000ABCDEFGHI00J000KLMNIOPQSR | ABCDEFGHI0 | ABCDEFGHI00J000KLMNIOPQSR |
| 00001A01A65463546354654006543606566565 | 1A01A65463 | 1A01A65463546354654006543606566565 |
| null | null | null |
| A | A | A |
| 0 | 0 | |
| 0A | A | A |
| 0000000000000000000 | 000000000 |