Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL to find upper case words from a column

Tags:

sql

oracle

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.

How do I achieve this?

like image 734
user1838000 Avatar asked Aug 01 '18 07:08

user1838000


People also ask

How do I get an uppercase query in SQL?

The UPPER() function converts a string to upper-case. Note: Also look at the LOWER() function.

How do you find upper and lower case in SQL?

The SQL UPPER function converts all the letters in a string into uppercase. If you want to convert a string to lowercase, you use the LOWER function instead.

How do you check for uppercase strings?

To check if a letter in a string is uppercase or lowercase use the toUpperCase() method to convert the letter to uppercase and compare it to itself. If the comparison returns true , then the letter is uppercase, otherwise it's lowercase. Copied!

How do I check if a string is lowercase in SQL?

SQL Server LOWER() Function The LOWER() function converts a string to lower-case.


1 Answers

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

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
like image 97
Aleksej Avatar answered Sep 17 '22 04:09

Aleksej