Help needed with String Operation

[shalinirk]

Hi,

I am using the below query to get Agent SHort NAme in the format
LastName Suffix-FirstName MiddleInitial

select RPAD(UPPER(DECODE(NVL(LENGTH(name_aka_lst_name),0) , 0, NULL, name_aka_lst_name) ||
DECODE(NVL(LENGTH(frst_name),0), 0, name_sffx,
DECODE(NVL(LENGTH(name_sffx),0), 0, ' ' || frst_name,' ' || name_sffx || '-'||frst_name)) ||
DECODE(NVL(LENGTH(mddl_name),0), 0, NULL, ' ' || mddl_name)),35) agnt_shrt_nm
from d where
tx_id = '123'


Now my problem is if the length of the field Agnt_Shrt_NM is greater than 35, I should trim the frst_name such that the length is restricted to 35.

Pls help me to find out how I can do this.

[debasisdas]

Hi
shalinirk
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.
It will help Experts in the forum in solving/underestanding your problem in a better way.

CAN YOU POST YOUT TABLE STRUCTURE PLEASE .

[shalinirk]

Hi Debasis,

I tried a lot. But it didn't materialize.

Please find the structure of the table

Name Null? Type
------------------------------- -------- ----
DRCTRY_ID NOT NULL NUMBER
NAME_PRFX VARCHAR2(4)
FRST_NAME VARCHAR2(30)
MDDL_NAME VARCHAR2(30)
MI VARCHAR2(1)
NAME_AKA_LST_NAME NOT NULL VARCHAR2(60)
NAME_SFFX VARCHAR2(10)
TX_ID VARCHAR2(9)

I want output in the format LastName Suffix-FirstName MiddleInitial.
And whenever the combination goes beyond 35 characters I want to trim the First Name instead of trimming from the end.

Using the below query I am able to get data in the format LastName Suffix-FirstName MiddleInitial. But am not able to include the trim function.
SQL> select drctry_id, RPAD(UPPER(DECODE(NVL(LENGTH(name_aka_lst_name),0) , 0, NULL, name_aka_lst_name) ||
2 DECODE(NVL(LENGTH(frst_name),0), 0, DECODE(NVL(LENGTH(name_sffx),0),0,NULL,' '||NAME_SFFX),
3 DECODE(NVL(LENGTH(name_sffx),0), 0,DECODE(NVL(LENGTH(frst_name),0),0,NULL,' '||FRST_NAME),
4 ' '||name_sffx || '-'||frst_name)) ||
5 DECODE(NVL(LENGTH(mddl_name),0), 0, NULL, ' '||substr(mddl_name,1,1))),35) agnt_shrt_nm
6 from d where
7 tx_id = '1232' ;

Thanks n Regards,
Shalini R K