用于从列中查找大写单词的SQL

I have a description column in my table and its values are:

我的表中有一个描述列，其值为：

This is a EXAMPLE
This is a TEST
This is a VALUE

I want to display only EXAMPLE, TEST, and VALUE from the description column.

我想在description列中只显示EXAMPLE，TEST和VALUE。

How do I achieve this?

我该如何实现这一目标？

7 个解决方案

#1

This could be a way:

这可能是一种方式：

-- a test case
with test(id, str) as (
select 1, 'This is a EXAMPLE' from dual union all
select 2, 'This is a TEST' from dual union all
select 3, 'This is a VALUE' from dual union all
select 4, 'This IS aN EXAMPLE' from dual
)
-- concatenate the resulting words
select id, listagg(str, ' ') within group (order by pos)
from (
    -- tokenize the strings by using the space as a word separator
    SELECT id,
           trim(regexp_substr(str, '[^ ]+', 1, level)) str,
           level as pos           
      FROM test t
    CONNECT BY instr(str, ' ', 1, level - 1) > 0
      and prior id = id
      and prior sys_guid() is not null
    )
-- only get the uppercase words
where regexp_like(str, '^[A-Z]+$')   
group by id

The idea is to tokenize every string, then cut off the words that are not made by upper case characters and then concatenate the remaining words.

这个想法是对每个字符串进行标记，然后切断不是由大写字符组成的单词，然后连接剩余的单词。

The result:

结果：

1    EXAMPLE
2    TEST
3    VALUE
4    IS EXAMPLE

If you need to handle some other character as an upper case letter, you may edit the where condition to filter for the matching words; for example, with '_':

如果你需要处理一些其他字符作为大写字母，你可以编辑where条件来过滤匹配的单词;例如，使用'_'：

with test(id, str) as (
select 1, 'This is a EXAMPLE' from dual union all
select 2, 'This is a TEST' from dual union all
select 3, 'This is a VALUE' from dual union all
select 4, 'This IS aN EXAMPLE' from dual union all
select 5, 'This IS AN_EXAMPLE' from dual
)
select id, listagg(str, ' ') within group (order by pos)
from (
    SELECT id,
           trim(regexp_substr(str, '[^ ]+', 1, level)) str,
           level as pos           
      FROM test t
    CONNECT BY instr(str, ' ', 1, level - 1) > 0
      and prior id = id
      and prior sys_guid() is not null
    )
where regexp_like(str, '^[A-Z_]+$')   
group by id

gives:

得到：

1   EXAMPLE
2   TEST
3   VALUE
4   IS EXAMPLE
5   IS AN_EXAMPLE

#2

It is possible to achieve this thanks to the REGEXP_REPLACE function:

由于REGEXP_REPLACE功能，可以实现这一目标：

SELECT REGEXP_REPLACE(my_column, '(^[A-Z]| |[a-z][A-Z]*|[A-Z]*[a-z])', '') AS Result FROM my_table

It uses a regex which replaces first upper case char of the line and converts every lower case char and space with blanks.

它使用正则表达式替换该行的第一个大写字母，并使用空格转换每个小写字母和空格。

#3

Here's another solution. It was inspired by Aleksej's answer.

这是另一种解决方案。它的灵感来自阿列克谢的答案。

The idea? Get all the words. Then aggregate only fully uppercased to a list.

这个想法？得到所有的话。然后只聚合完全大写到列表。

Sample data:

样本数据：

 create table descriptions (ID int, Description varchar2(100));

 insert into descriptions (ID, Description) 
 select 1 as ID, 'foo Foo FOO bar Bar BAR' as Description from dual 
 union all select 2, 'This is an EXAMPLE TEST Description VALUE' from dual
 ;

Query:

查询：

 select id, Description, listagg(word, ',') within group (order by pos) as UpperCaseWords
 from (
     select 
      id, Description,
      trim(regexp_substr(Description, '\w+', 1, level)) as word,
      level as pos           
     from descriptions t
     connect by regexp_instr(Description, '\s+', 1, level - 1) > 0
       and prior id = id
       and prior sys_guid() is not null
     )
 where word = upper(word)
 group by id, Description

Result:

结果：

ID | DESCRIPTION                               | UPPERCASEWORDS    
-- | ----------------------------------------- | ------------------
 1 | foo Foo FOO bar Bar BAR                   | FOO,BAR           
 2 | This is an EXAMPLE TEST Description VALUE | EXAMPLE,TEST,VALUE

#4

Try this:

尝试这个：

SELECT SUBSTR(column_name, INSTR(column_name,' ',-1) + 1)
FROM your_table;

#5

This should do the trick:

这应该是诀窍：

SELECT SUBSTR(REGEXP_REPLACE(' ' || REGEXP_REPLACE(description, '(^[A-Z]|[a-z]|[A-Z][a-z]+|[,])', ''), ' +', ' '), 2, 9999) AS only_upper
FROM ( 
    select 'Hey IF you do not know IT, This IS a test of UPPERCASE and IT, with good WILL and faith, Should BE fine to be SHOWN' description
    from dual 
)

I have added condition to strip commas, you can add inside that brakets other special characters to remove.

我添加条件来删除逗号，你可以在其中添加其他要删除的特殊字符。

ONLY_UPPER
-----------------------------------
IF IT IS UPPERCASE IT WILL BE SHOWN

#6

This is a function based on some of the regular expression answers.

这是一个基于一些正则表达式答案的函数。

create or replace function capwords(orig_string varchar2)
return varchar2
as
out_string varchar2(80);
begin
  out_string := REGEXP_REPLACE(orig_string, '([a-z][A-Z_]*|[A-Z_]*[a-z])', '');
  out_string := REGEXP_REPLACE(trim(out_string), '(  *)', ' ');
  return out_string;
end;
/

Removes strings of upper case letters and underscores that have lower case letters on either end. Replaces multiple adjacent spaces with one space. Trims extra spaces off of the ends. Assumes max size of 80 characters.

删除大写字母和下划线的字符串，两端都有小写字母。用一个空格替换多个相邻空格。从末端修剪额外的空间。假设最大大小为80个字符。

Slightly edited output:

略有编辑的输出：

>select id,str,capwords(str) from test;

        ID STR                            CAPWORDS(STR)
---------- ------------------------------ ------------------
         1 This is a EXAMPLE              EXAMPLE
         2 This is a TEST                 TEST
         3 This is a VALUE                VALUE
         4 This IS aN EXAMPLE             IS EXAMPLE
         5 This is WITH_UNDERSCORE        WITH_UNDERSCORE
         6 ThiS IS aN EXAMPLE             IS EXAMPLE
         7 thiS IS aN EXAMPLE             IS EXAMPLE
         8 This IS wiTH_UNDERSCORE        IS

#7

If you only need to "display" the result without changing the values in the column then you can use CASE WHEN (in the example Description is the column name):

如果您只需要“显示”结果而不更改列中的值，那么您可以使用CASE WHEN（在示例Description中是列名称）：

Select CASE WHEN Description like '%EXAMPLE%' then 'EXAMPLE' WHEN Description like '%TEST%' then 'TEST' WHEN Description like '%VALUE%' then 'VALUE' END From [yourTable]

The conditions are not case sensitive even if you write it all in uppercase. You can add Else '<Value if all conditions are wrong>' before the END in case there are descriptions that don't contain any of the values. The example will return NULL for those cases, and writing ELSE Description will return the original value of that row.

即使您以大写形式全部写入条件，条件也不区分大小写。如果存在不包含任何值的描述，您可以在END之前添加Else' <值，如果所有条件都错误> 。该示例将为这些情况返回NULL，并且编写ELSE描述将返回该行的原始值。

It also works if you need to update. It is simple and practical, easy way out, haha.

如果您需要更新它也可以。哈哈，简单实用，简单易行。