boomcubist boomcubist - 6 months ago 18
SQL Question

Trim first zero after dot twice sql query

I'm trying to compare assembly versions using SQL Server, however there can be more than one version returned and I need it be in a six digit format.

For example, the assembly version

2.00.0001
and I need that to be returned as
2.0.1
.

There could be versions like
1.01.0031
that I would need to be
1.1.31
.

This works but is there a better way of doing it?

select left(left([output],9),1)+'.'+substring(left([output],9),3,1)+'.'+substring(right(left([output],9),1),1,1)

Answer

Using ParseName function, you can achieve this. Try this -

DECLARE @val VARCHAR(100) = '01.10.0031'

SELECT CONVERT(VARCHAR, CONVERT(INT, PARSENAME(@val, 3))) + '.' + 
     CONVERT(VARCHAR, CONVERT(INT, PARSENAME(@val, 2))) + '.' + 
     CONVERT(VARCHAR, CONVERT(INT, PARSENAME(@val, 1)))

Result

1.10.31