CODEWITHSUNDEEP
oracle-sqldeveloper
Vinay Kumar
Vinay Kumar

Reputation: 1

To check if last name is part of first name

I am working on Data standardization rules, and one of the rules says, "If the last name is part of first name, then remove the last name from first name". my Query- how do i check if first name column has the last name in it using oracle sql developer?

I tried using :

select fst_nm, lst_nm from emp where fst_nm = fst_nm || lst_nm  ;

but this query returns '0' results.

Also, I tried another query:

select fst_nm, lst_nm, regexp_substr(fst_nm, '[^ ]+',1,1) from emp ;

I tried using the below query

select fst_nm, lst_nm from emp where fst_nm = fst_nm || lst_nm  ;

but this query returns nothing, I mean '0' results.

Also, I tried another query:

select fst_nm, lst_nm, regexp_substr(fst_nm, '[^ ]+',1,1) from emp ;

expected result is:

fst_nm = john smith ;
lst_nm = smith

Actual result showing up is :

fst_nm = john ;  
lst_nm = smith

Please help

Upvotes: 0

Views: 31

Answers (2)

saurabh rai
saurabh rai

Reputation: 16

You can use below logic

select length('saurabh rai'),instr('saurabh rai',' '),case when length('saurabh rai') > instr('saurabh rai',' ') then substr('saurabh',1,instr('saurabh rai',' ')-1) else 'saurabh rai' end as a from dual;

Update emp set fst_nm=(Case when length(fst_nm) > instr(fst_nm, ' ') then substr(fst_nm,1,instr(fst_nm,' ')-1) else fst_nm end);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

You should be able to just do a blanket replace on the entire table:

UPDATE emp
SET fst_nm = REPLACE(fst_nm, lst_nm, '');

The reason this should work is that for those records where the last name does not appear as part of the first name, the replace would have no effect. Otherwise, the last name would be stripped from the first name.

Upvotes: 1

Related Questions