CODEWITHSUNDEEP
sqloracle-databasesubstr
BlackCat
BlackCat

Reputation: 2044

How to get values using CASE statements in SELECT clause?

I have table Table1 that contains a column: Column1

Column1
AAA Machine-1
BBB Machine-2
CCC Machine-3

I need a output as below based on values of Column1

Process Machine
First Process Machine-1
Second Process Machine-2
Third Process Machine-3

How can I achieve these values using CASE statement? or, is there other way to get the results?

Upvotes: 0

Views: 77

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use date format element suffix for ordinal numbers along with ROW_NUMBER() analytic function in order to return Process column and REGEXP_SUBSTR() regular expression function to extract Machine column through splitting by space character such as

SELECT INITCAP(TO_CHAR(TO_DATE(ROW_NUMBER() OVER(ORDER BY column1) || '-01-01',
                               'YYYY-MM-DD'),
                       'YYYYSPth')) || ' Process' AS "Process",
       REGEXP_SUBSTR(column1, '[^ ]+', 1, 2) AS "Machine"
  FROM t

Demo

Upvotes: 0

persian-theme
persian-theme

Reputation: 6638

you can use following query

in oracle:

SELECT 
  case SUBSTR(COLUMN1,INSTR(column1,'Machine-',1) + LENGTH('Machine-'),LENGTH(COLUMN1))
    when '1' then 'First Process'
    when '2' then 'Second Process'
    else 'Third Process'
 end as Process,
  SUBSTR(COLUMN1, 5, LENGTH(COLUMN1)) AS Machine
FROM TABLE1

in sql

    SELECT 
      case SUBSTRING(COLUMN1,CHARINDEX(column1,'Machine-',1) + LEN('Machine-'),LEN(COLUMN1))
        when '1' then 'First Process'
        when '2' then 'Second Process'
        else 'Third Process'
     end as Process,
      SUBSTRING(COLUMN1, 5, LEN(COLUMN1)) AS Machine
    FROM TABLE1

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

This is one option:

SQL> with table1 (column1) as
  2    (select 'AAA Machine-1' from dual union all
  3     select 'BBB Machine-2' from dual
  4    )
  5  select
  6    'Process-' || substr(column1, 1, 1) as process,
  7    substr(column1, instr(column1, ' ') + 1) as machine
  8  from table1;

PROCESS   MACHINE
--------- -------------
Process-A Machine-1
Process-B Machine-2

SQL>

Upvotes: 1

Related Questions