SQL从列中查找大写单词 [英] SQL to find upper case words from a column

问题描述

我的表格中有一个description列，其值是:

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

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

我只想显示描述列中的EXAMPLE，TEST和VALUE.

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

我该如何实现?

推荐答案

这可能是一种方法:

-- 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.

结果:

1    EXAMPLE
2    TEST
3    VALUE
4    IS EXAMPLE

如果您需要将其他字符作为大写字母处理，则可以编辑where条件以过滤匹配的单词；例如，使用'_':

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 '_':

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

给予:

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

这篇关于SQL从列中查找大写单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！