Using PATINDEX to verify and replace-CodePudding

I have a query that converts float to string with pre-assigned len and precision and removes trailing and leading zeroes.

SELECT 
replace(ltrim(replace(replace(rtrim(replace(str(2005.012, 17,9), '0', ' ')), ' ','0'),'0',' ')), ' ','0')

Output:

2005.012

But the issue with this query is if I have a data 0.0 It's returning just a decimal.

SELECT 
replace(ltrim(replace(replace(rtrim(replace(str(0.0, 17,9), '0', ' ')), ' ','0'),'0',' ')), ' ','0')

Output:

Is there a way I can use patindex to check if the output is "." then replace it with 0.0? Or any other way to remove leading and trailing zero without using cast or round (its messing up with the decimal precision as I'm comparing data from 2 database its not an option for me).

Any idea or help would be appreciated! Thanks

CodePudding user response：

You could implement something like the following that just expands on your current string-replace solution:

with n as ( /* sample data */
    select str(2005.182, 17,9) [value] union all 
    select Str(0.0, 17,9)
)

select Iif(v = '.', '0.0', v) Result
from n
cross apply (values(replace(ltrim(replace(replace(rtrim(replace([value], '0', ' ')), ' ','0'),'0',' ')), ' ','0')))v(v);