How to trim leading zeros of each value in a concatenated field in SQL Server

Question

I have a requirement as below.

Input:

00113|0267|0139

Expected Output:

113|267|139

Input is directly coming from a file and it's not a generated field

As of now i am using below code to split the filed into multiple values, then trimming it using Patindex and Substring functions, finally concatenating them again

Substring(col, 1,charindex('|',col)-1) part1,
Substring(col,charindex('|',col)+1,(charindex('|', col, charindex('|', col, 1)+1)-charindex('|',col)-1)) part2,
Substring(col,charindex('|', col, charindex('|', col, 1)+1)+1,len(col)) part3

Let me know if there is any better way to achieve this without using these many string parsing functions

Answer 1

try the following:

declare @i int = 0

while (patindex('%|0%', '|'+@str) > 0)
begin
    set @str = ( replace(substring('|'+ @str, 1, patindex('%|0%', '|'+@str)+1 ), '|0', '|') + substring(@str, patindex('%|0%', '|'+@str)+1, len(@str)) )
    set @i += 1
end

select stuff(@str, 1, @i, '')

you can also use string_split like below:

select STUFF((SELECT  '|' + convert(varchar(100), convert(int, nullif(value, '')))
                  FROM string_split(@str, '|') t
                   FOR XML PATH ('')) , 1, 1, '') Grouped_Value

please find the db<>fiddle here.