CREATE TABLE test(
message VARCHAR2(500)
);INSERT INTO test VALUES ('successfully sent. count:3, no:125');
INSERT INTO test VALUES ('Action done. count:523, no:789456');select * from test
MESSAGE
----------------------------------
successfully sent. count:3, no:125
Action done. count:523, no:789456
from the above table i would like to get the output as below (remove the data after first period(0) and before first comma(,))
MESSAGE
----------------------------------
successfully sent. no:125
Action done. no:789456
Another question based on an extension to the above question
If we have another row as below in the same table
INSERT INTO test VALUES ('Good sent. no:1254985');select * from test
MESSAGE
----------------------------------
successfully sent. count:3, no:125
Action done. count:523, no:789456
Good sent. no:1254985
And i would like to get output as
MESSAGE
----------------------------------
successfully sent. no:125
Action done. no:789456
Good sent. no:1254985 -- Note that for the first two records, count is trimmed off and the third one comes as is because it doesn't have the string (count:xx)part.
Thanks in advance
There are many ways you could do this. Broadly there are two methods:
- Use a regular expression to remove the unwanted characters
- Substr the values to preserve the parts you want to keep
Here's two implementations of these:
select regexp_replace ( message, '\..*,', '.' ) regex,
substr ( message, 1, instr ( message, '.' ) ) ||
substr ( message, instr ( message, ',' ) + 1 ) subin
from test;
REGEX SUBIN
successfully sent. no:125 successfully sent. no:125
Action done. no:789456 Action done. no:789456 The regex finds all the characters between period and comma
Substr returns the characters up to the first period. Then concatenates all the characters after the comma
Whichever one you choose, ensure you test thoroughly! It's often the case there are some strings that break these rules. e.g. having extra periods or commas.