I have a table name Test_name.which has two columns full_name and nick_name.I have values only in full_name where as Nick_name column is blank which i need to update as per full_name.I have more than 1000 distinct full_name.I want to update the ccolumn according to different logic which all depends upon underscore(_) in full_name.to illustrate i have full_name= 'james_9333_mathew_rohn' it means full_name has more than 1 underscore in name then i want nick_name to be ''james_rohn'.but if there is underscore at last in full_name( 'james_9333_mathew_rohn_' ) then result could be either 'james_rohn' or 'james_rohn_' both are fine for this case.when we have only 1 undersore like 'james_rohn' then nick_name should be 'james_rohn' and but if there is undersocre at last like 'james_rohn_' then result could be either either 'james_rohn' or 'james_rohn_' both are fine.please help me to solve this.

I want my final table to be

insert into test_name values( 'james_9333_mathew_rohn','james_rohn')

,('james_9333_mathew_rohn_','james_rohn_'),

('james_rohn','james_rohn'),

('james_rohn_','james_rohn_')