CODEWITHSUNDEEP
regexoracle-database
Proxonator
Proxonator

Reputation: 13

Oracle - How to get string after specified length and character

I have multiple strings concated with ';'. I have to insert those words in a column which is unfortunatly limited to 500 characters. I want to cut the string at the last word before breaching the 500 character mark.

I tried using REGEXP_SUBSTR (SELECT REGEXP_SUBSTR(col, '[^;]+', 1, 1)) but this query only returns the first string.

Altering the table is no option.

Upvotes: 0

Views: 213

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You could try:

SELECT
    REGEXP_REPLACE(SUBSTR(col, 1, 500), ';[^;]*$', '') AS col_out
FROM yourTable;

The logic here is to first truncate to 500 characters, using SUBSTR. Then we remove any possible trailing word by removing the final semicolon and following (perhaps partial) word.

Upvotes: 1

Related Questions