How to replace suffix of data?

[nir_s]

Hi folks,

I need to replace the suffix "_TST" with "_TRN1" in a column .
For example , if the column contains the value xxx_tst I need to replace it to xxx_trn1.
How to do it?

Thanks in advance,
Nir

[PAVB]

How about something like...

update your_table
set    your_column = replace(your_column, '_TST', '_TRN1')
where  your_column like '%_TST'
;

[nir_s]

Hi PAVB,

Thanks for your reply but I needed to replace only the appearances in the end of the word.
If the string "_TEST" (by mistake I wrote "_TST") appears in the middle of the word ,then the "replace" command will replace all the appearances.
I've just solved my problem as the following:

update table
set x=SUBSTR(x,1,length(x)-4)||'TRN1'
where substr(x,instr(x,'_',-1,1)+1,length(x)-instr(x,'_',-1,1))='TEST';

Best regards,
Nir

[PAVB]

well... no literal on the middle of the string was bound to be changed by my script; look at the where clause, it filters out everything that does not have your desired literal at the end of the string.

where  your_column like '%_TST'

Either way, I'm glad you solved the problem.

[nir_s]

Thanks for your simple "WHERE" clause.