how to replace a part of a string-CodePudding

I have a field that always consists of three values with dots between them.

like:

123456.22.aaa
23455.11.bbb

I would like to write a query that will update only the first part of the above field with a different value, so I will have them like:

111111.22.aaa
222222.11.bbb

That is - replace only the first part - up to the first dot of the field. How can I get the first part out of this string?

note: there is no pattern for this value, I cannot search for a similar string and replace it. The only indication is that I need to replace the sub-string up to the first ..

CodePudding user response：

For this very specific format, use substring_index with -2 as the last parameter:

select concat('new value.', substring_index('123456.22.aaa', '.', -2))
-- new value.22.aaa