I am trying to query the name field in the Oracle DB like below

select NAME, Substr(NAME,1,instr(NAME,' ') - 1) as SHORTNAME from rm_room

what I am trying is if the Name field has space between the string the select only charcters on the left of the space. I am expecting the above query to return

but the issue is if the name field doesnot have space then it is returning null in the SHORTNAME Field

Can we use if condition in the select clause if so how can I check that

CodePudding user response：

Regular expressions (see line #7):

SQL> with rm_room (name) as
  2    (select 'AX1 BD' from dual union all
  3     select 'A1'     from dual union all
  4     select 'BC W1'  from dual
  5    )
  6  select name,
  7    regexp_substr(name, '^\w ') shortname
  8  from rm_room    ;

NAME   SHORTN
------ ------
AX1 BD AX1
A1     A1
BC W1  BC

SQL>

CodePudding user response：

Use a CASE expression:

select NAME,
       CASE instr(NAME,' ')
       WHEN 0
       THEN name
       ELSE Substr(NAME,1,instr(NAME,' ') - 1)
       END as SHORTNAME
from   rm_room

Which, for the sample data:

CREATE TABLE rm_room (NAME) AS
SELECT 'AX1 BD' FROM DUAL UNION ALL
SELECT 'A1' FROM DUAL UNION ALL
SELECT 'BC W1' FROM DUAL;

Outputs:

NAME	SHORTNAME
AX1 BD	AX1
A1	A1
BC W1	BC

You could also use a regular expression:

select NAME,
       REGEXP_SUBSTR(name, '^[^ ] ') as SHORTNAME
from   rm_room

However, they are much slower than simple string functions (even if the code is shorter).

db<>fiddle here

CodePudding user response：

you could just use the NVL function which sets a default value instead of null

select NAME, NVL(Substr(NAME,1,instr(NAME,' ') - 1),NAME) as SHORTNAME from rm_room

CodePudding user response：

Check if the name contains a space or not

select NAME,
Case WHEN instr(NAME,' ') > 0 THEN Substr(NAME,1,instr(NAME,' ') - 1) 
Else NAME End as SHORTNAME 
from rm_room

It is preferable to use the Trim function to cut any spaces at the beginning or end of the room name

select NAME,
Case WHEN instr( TRIM(NAME),' ') > 0 THEN Substr(TRIM(NAME),1,instr(TRIM(NAME),' ') - 1) 
Else TRIM(NAME) End as SHORTNAME 
from rm_room