oncik oncik - 1 month ago 5
SQL Question

How to trim out letter in the column

I don't know the effective way to trim out letter in the name. For example, the

f_name
column have
Jenny, Johnny, Doe, Ken, Smith
.

I wanted to trim out the letter in these name so it consist only the first 2 letter. Like
Je, Jo, Do, Ke, Sm
as the output for the new column.

But the letter in these name don't have equal number of letter, like
Johnny
have 6 letter and
John
have 4 letter.

Is there any effective way to trim the uneven character's length without count all the character's length in
f_name
and place all the condition to trim all names. Like these below.

CASE WHEN LENGTH(f_name) > 4 THEN LTRIM(f_name, 2)

Answer

For Oracle use substr():

with data (f_name) as (
  select 'Jenny' from dual union all
  select 'Johnny' from dual union all
  select 'Doe' from dual union all
  select 'Ken' from dual union all
  select 'Smith' from dual
)
select substr(f_name, 1, 2)
from data

Returns:

SUBSTR(F_NAME,1,2)
------------------
Je                
Jo                
Do                
Ke                
Sm