Oracle insert character into a string

Question

I have this table of rows

RowA
______
ABC123
DEF432
WER677
JKL342

how can I add a '_' in between the record using oracle to this? Assuming to add on the last 4 character.

RowA
______
ABC_123
DEF_432
WER_677
JKL_342

Answer 1

You would try something like:

Update Table_name set table_column = substr(table_column, 1, 3) || '_' || substr(table_column, 4);

The SUBSTR functions allows you to extract a substring from a string. The syntax for the SUBSTR function is:

SUBSTR( string, start_position, [ length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

Answer 2

Another approach, using regexp_replace() regular expression function:

 select regexp_replace(RowA, '^([[:alpha:]]{3})', '\1_') as res
   from your_table

Result:

RES
----------
ABC_123
DEF_432
WER_677
JKL_342

SQLFiddle Demo