I have a dataset where the values are different, and I want to bring them into a single format.The values are stored as varchar For ex. 1st Case: 1.23.45 should be 123.45 2nd Case: 125.45 should be 125.45

The first one, has two decimals. I want to remove the first decimal only(if there are 2) else let the value be as it is. How do I do this?

I tried using replace(Qty,'.',''). But this is removing of them.

CodePudding user response：

I think this can do (although I am not 100% sure about corner cases)

SET Qty = SUBSTRING(Qty, 1, LOCATE(Qty, '.') - 1)   SUBSTRING(Qty, LOCATE(Qty, '.')   1, LENGTH(Qty) - LOCATE(Qty, '.') - 1)
WHERE LENGTH(Qty) - LENGTH(REPLACE(Qty, '.', '')

CodePudding user response：

You can use a regular expression to handle this case.

Assuming there are only two decimals in your string the below query should be able to handle the case.

select (value,'^(\d )(\.)?(\d \.\d )$',concat('$1','$2')) as a

Here we are matching a regular expression pattern and capturing the following

1. digits before first decimal occurrence in group one
2. digits before and after last decimal occurrence including the last decimal in group two.

Following that we are concatenating the two captured groups.

Note that the first decimal has been made optional using ? character and hence we are able to handle both type of cases.

Even if there are more than two decimal cases, I believe a properly constructed regular expression should be able to handle it.