CODEWITHSUNDEEP
sqloracle-database
Topcio
Topcio

Reputation: 13

Add string before number in string

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.

Upvotes: 0

Views: 146

Answers (3)

Peter
Peter

Reputation: 1196

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
;+

Upvotes: 3

Miroslav Duník
Miroslav Duník

Reputation: 606

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

Upvotes: 1

Topcio
Topcio

Reputation: 13

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

Upvotes: 0

Related Questions