PL/SQL过程:将大写名称更新为initcaps，并对某些值进行特殊处理 [英] PL/SQL procedure: UPDATE uppercase names to initcaps, with special handling for some values

问题描述

我需要执行以下操作的过程:原始名称:AMSTERDAM新名称:Amsterdam. 我为此做了以下过程:

I need a procedure to do something like this: original name: AMSTERDAM new name: Amsterdam. I made this procedure for it:

create or replace PROCEDURE NaamRoutine
is
BEGIN
  update Gemeentenew
  set gemeentenaam = lower(gemeentenaam);
  update Gemeentenew
  set gemeentenaam = initcap(gemeentenaam); 
END;

问题是有几个名字以'S GRAVENHAGE开头，并且必须是'Gravenhage.

The problem is there are a couple of names that start like 'S GRAVENHAGE and need to be 's Gravenhage.

推荐答案

假定仅对像'S...这样的名称进行特殊处理，添加简单的REPLACE应该可以.顺便说一句，您不需要两个单独的UPDATE语句-INITCAP会自动将非初始字符转换为小写.

Assuming the special handling is necessary only for names like 'S..., adding a simple REPLACE should work. BTW, you don't need two separate UPDATE statements - INITCAP will automatically convert non-initial characters to lowercase.:

with v_data(name) as (
  select 'AMSTERDAM' from dual union all
  select '''S GRAVENHAGE' from dual union all
  select 'DEN HAAG' from dual union all
  select 'IJSLAND' from dual
  )
select 
  replace(nls_initcap(name, 'NLS_SORT=xDutch'), '''S', '''s') name
from v_data

Name
----
Amsterdam
's Gravenhage
Den Haag
IJsland

这将用's替换所有出现的'S.如果您还需要处理其他情况，建议您尝试REGEXP_REPLACE().

This will replace all occurrences of 'S with 's. If you need to handle other cases as well, I suggest you try REGEXP_REPLACE().

函数 NLS_INITCAP 帮助还有一些全球化问题.例如，它在IJSLAND中将I和J都大写.但这对'S名称没有帮助.我不确定这是Oracle全球化功能的错误还是这些城市名称都是例外.

The function NLS_INITCAP helps with some globalization issues. For example, it capitalizes both the I and the J in IJSLAND. But it doesn't help with 'S names. I'm not sure if that is a bug with Oracle's globalization functions or if those city names are all exceptions.

这篇关于PL/SQL过程:将大写名称更新为initcaps，并对某些值进行特殊处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！