I think you may use an update like below:
for r in (select * from x_table)
update x_table t set
where replace(t.name_column,' ','')=trim(r.name_column)||trim(r.name_column);
This supposing name_column is the column where those names are stored and new_name_column is another column where you store what you finally need to display.
That's what comes into my mind so far, as to be as general as it can be.
Hello, I have list of names which some of them need write case:
For example highlighted rows should be named as one not two:
case when name = 'Дархан' or name = 'Дархан Дархан' then 'Дархан'
the list will go on.
The question is do I have to write case for every single names regardless it should be renamed or not?
like case when name = a or name = b then a
case when name = c then c
It's not clear what you want to do.
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 126.96.36.199.0).
See the forum FAQ: Re: 2. How do I ask a question on the forums?
If you want to eliminate duplicates, then use SELECT DISTINCT.
If sometimes two (or more) strings really are different, but you want to treat them as duplicates, then you can use a CASE expression similar to what you posted, but only list the ones that need to be renamed., and use ELSE for all the others.
For example, if you want a list of distinct enames from the scott.emp table, but you want to treat a few names as duplicates of others, then you can do something like this:
WHEN 'SMITH' THEN 'JONES'
WHEN 'ALLEN' THEN 'SAINAA'
WHEN 'FUBAR' THEN 'FOO'
ORDER BY new_name
The table contains both 'SMITH' and 'JONES', but the results have only 'JONES'.
The table odes not contain 'FUBAR'; that doesn't cause any problem.
If you're ever tempted to us a CASE expression that needs lots of WHEN clauses, then maybe you ought to have the mapping stored in a table.