ORACLE： – 通过删除第一个单词后面的空格来选择名字

ORACLE： –
在表中,名字如下

Binda  E                                                                             
Reven   O                                                                               
Drew  J                                            
ANDAL J                                                                                       
WILL Lee                                            
Chad Hardee

我想以下列格式选择名字

Binda                                                                               
Reven                                                                                  
Drew                                               
ANDAL                                                                                         
WILL                                              
Chad

我使用以下查询,但没有运气

SELECT  first_name,SUBSTR(first_name,REGEXP_INSTR('first_name','[^ ]+',1,1) ) FROM contact.user_names

请建议.

解决方法

with t AS
    (
    SELECT 'Binda  E   ' as first_name FROM dual                                                                          
    union
    SELECT 'Reven   O  ' as first_name FROM dual                                                                             
    union
    SELECT 'Drew  J    ' as first_name FROM dual                                        
    union
    SELECT 'ANDAL J    ' as first_name FROM dual                                                                                   
    union
    SELECT 'WILL Lee   ' as first_name FROM dual                                         
    union
    SELECT 'Chad Hardee' as first_name FROM dual 
    union
    SELECT 'foobar' as first_name FROM dual 

    )
    SELECT NVL(substr(first_name,' ')),first_name) FROM t
    ;

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!