How to format String with a pattern

Question

I want to format my string in my select statement but I cant seem to find the syntax for this pattern.

I have a column of card_num with 16 digits and i want to have a dash(-) after the first four digits.

Original card_num: 1234567891234567

Desired output: 1234-567891234567

I think regex can do this but cant find elsewhere the right code for this pattern.

Any idea how to do this? Thanks.

Answer 1

You could use substr() and concatenation (Oracle 12c): select the first 4 characters, then concatenate '-' and the rest of the string.

create table t
as
select '1234567891234567' card_num from dual;
select card_num
, substr(card_num, 1,4) || '-' || substr(card_num, 5, length(card_num) - 4) 
 formatted
from t;
SQL> select card_num
 2 , substr(card_num, 1,4) || '-' || substr(card_num, 5, length(card_num) - 4) 
 3 formatted
 4 from t;
CARD_NUM FORMATTED 
1234567891234567 1234-567891234567 

... the table is "overkill" for this example - but it may make it easier to experiment/adjust your query.

• Blank: I just realized that substr is a lot way better for this example. It slipped my mind. Thanks!

Answer 2

select
 regexp_replace('1234567891234567', '(.{4})(.{12})', '1円-2円') as card_number
from dual;
CARD_NUMBER
-----------------
1234-567891234567

• Vérace: I have a feeling that @stefan 's soution would be more efficient - no?
• Balazs Papp: @Vérace Maybe, I experienced that substr and similar functions performed better than regexp functions in simple string manipulation. But OP specifically mentioned regex.
• Vérace: True - plus one!
• Blank: Substr slipped my mind. Im too focused on regex. My bad. I just realized it's more efficient. I already upvote your answer. Thanks anyways.