I am looking for solutions.
I have string something like "AAAAAA 8 BBBBBB 5"
I must replace space before numbers with -, and other space with _-_.
Can some one help me.
3 Answers
I would do it with 2 regexp_replace like so:
with the_string as (
select 'AAAAAA 8 BBBBBB 5' s from dual
),
the_string_with_numbers_done as (
select regexp_replace (s,' (\d)','-\1') s from the_string
)
select regexp_replace (s,' ','_-_') from the_string_with_numbers_done
;
Here is info on regexp_replace: https://docs.oracle.com/database/121/SQLRF/functions163.htm#SQLRF06302
And this is the site I like to use to work on regular expressions: https://regexr.com/
Edit: Slightly changed version based on later comments from OP - for changed blank handling as OP seems to want to have successive blanks handled as one in some/all cases. The requirements are still not 100% clear to me though, especially what blank blank number should become: dash number or underscore dash underscore dash number. The below version does the former (dash number). HTH
with the_string as (
select 'AAAAAA 8 BBBBBB 5' s from dual
),
the_string_with_numbers_done as (
select regexp_replace (s,' +(\d)','-\1') s from the_string
)
select regexp_replace (s,' +','_-_') from the_string_with_numbers_done
;+
1 Comment
William Robertson
From Topcio's answer it seems there can be multiple spaces, so might be worth extending this answer with
' +' etc.You can use REGEXP_REPLACE here.
Example:
select regexp_replace(
regexp_replace('AAAAAA 8 BBBBBB 5', '\s([[:digit:]]+)', '-\1', 1, 0),
'([[:digit:]]+\s)', '\1_-_', 1, 0) as str
from dual;
STR
--------------------
AAAAAA-8 _-_BBBBBB-5
More info here: https://docs.oracle.com/database/121/SQLRF/functions163.htm#SQLRF06302
1 Comment
Miroslav Duník
Barbaros - the question was about PL/SQL, but yes, this SQL revision is neat.
I also added
with the_string as (
select TEST s from AA_TEST
),
**multiple_spaces as (
select regexp_replace (s, '\s{2,}', ' ') s from the_string
),**
the_string_with_numbers_done as (
select regexp_replace (s,' (\d)','_\1') s from multiple_spaces
)
select regexp_replace (s,' ','_-_') from the_string_with_numbers_done
;
And now, everything work perfect
2 Comments
William Robertson
Wouldn't you just include the multiple space pattern in one of the existing regexes,rather than adding a whole new layer?
Topcio
I think not, because everyone does something else, but I do not know so much, so I may be wrong
replacea' 'with a'-'…