Remove trailing zeroes from the middle of a string

Transact-SQL
1

I have only been working with SQL for a few months. I am trying to strip trailing zeroes from a version number in the middle of a string.
For example, I have a record that shows "7-zip 19.00 (x64 Edition)" I need to make a column that shows it as "7-zip 19 (x64 Edition)"
I wouldn't normally manipulate this data, but I'm working with some terrible data from the start.
For clarification, I am not trying to make changes to the stored data. I just need to show a new column in my query.

Thanks

2

that type of string manipulation will come to bite your butt later on proceed with caution

image

2 Likes
3

A simple REPLACE of '.00' will fail in the face of 19.01 or 19.02 or 8.50.

Try running a SELECT to see how many suffixes other than '.00' you need to deal with.

select version
from table
where version like '%[0-9].[0-9][1-9]%'
1 Like
4

any approach with string parsing is fraught with mines when it comes to data manipulation.
Such as the solution you proposed :wink:

image
image957×301 19.9 KB

select version
from (select '7-zip 19.00 (x64 Edition)' as version) a
where version like '%[0-9].[0-9][0-9]%'
5

Yes. Well aware string manipulation is fraught with danger. Your point?

My suggestion was to run a query to try and identify the scope of the issue, the better to deal the inevitable mines.