Home » SQL & PL/SQL » SQL & PL/SQL » How to extract first 3 characters from each word in a string/sentence and separate with underscore? (Oracle 11g, Win XP)
icon5.gif  How to extract first 3 characters from each word in a string/sentence and separate with underscore? [message #599124] Tue, 22 October 2013 05:58 Go to next message
raksh1986
Messages: 3
Registered: October 2013
Location: Bangalore
Junior Member
Hi,

I have some strings like
'Net Amount Payable by an Individual',
'Net Amount Payable by an Individual+Tax',
'Total Amount Payable towards Service',
'Total Amount Payable towards Service.+Tax'

I need to extract the first three letters from each word and separate them using an underscore. The output should be as follows for the above strings -

Net_Amo_Pay_by_an_Ind
Net_Amo_Pay_by_an_Ind_Tax
Tot_Amo_Pay_tow_Ser
Tot_Amo_Pay_tow_Ser_Tax

I request to let me know the way to get the ouput as mentioned.

Thanks.
Re: How to extract first 3 characters from each word in a string/sentence and separate with underscore? [message #599130 is a reply to message #599124] Tue, 22 October 2013 06:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Use REGEXP_REPLACE.

SY.
Re: How to extract first 3 characters from each word in a string/sentence and separate with underscore? [message #599252 is a reply to message #599130] Wed, 23 October 2013 05:22 Go to previous messageGo to next message
raksh1986
Messages: 3
Registered: October 2013
Location: Bangalore
Junior Member
Hi Solomon,

The Query below helped me to achieve partially.

SELECT REGEXP_REPLACE(REGEXP_REPLACE(TRANSLATE('Total Amount Payable towards Service','.+',' '),'(^| )([[:alnum:]]{3})([^ ])*','\2_'),'\s*_\s*$', '') FROM DUAL;

I was not able to achieve the same for the other 3 as mentioned in the list.

Please help me.

Thanks.



[Edit MC: fix [[ to wiki link conversion]

[Updated on: Wed, 23 October 2013 05:44] by Moderator

Report message to a moderator

Re: How to extract first 3 characters from each word in a string/sentence and separate with underscore? [message #599261 is a reply to message #599252] Wed, 23 October 2013 06:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
raksh1986 wrote on Wed, 23 October 2013 06:22
The Query below helped me to achieve partially.


Gross overkill:

column new_str format a30
with t as (
           select 'Net Amount Payable by an Individual' str from dual union all
           select 'Net Amount Payable by an Individual+Tax' from dual union all
           select 'Total Amount Payable towards Service' from dual union all
           select 'Total Amount Payable towards Service.+Tax' from dual
          )
select  str,
        rtrim(regexp_replace(str,'(\w{1,3})\w*(\W+|$)','\1_'),'_') new_str
  from  t
/

STR                                       NEW_STR
----------------------------------------- ------------------------------
Net Amount Payable by an Individual       Net_Amo_Pay_by_an_Ind
Net Amount Payable by an Individual+Tax   Net_Amo_Pay_by_an_Ind_Tax
Total Amount Payable towards Service      Tot_Amo_Pay_tow_Ser
Total Amount Payable towards Service.+Tax Tot_Amo_Pay_tow_Ser_Tax

SCOTT@orcl > 


SY.
Re: How to extract first 3 characters from each word in a string/sentence and separate with underscore? [message #599277 is a reply to message #599261] Wed, 23 October 2013 08:12 Go to previous message
raksh1986
Messages: 3
Registered: October 2013
Location: Bangalore
Junior Member
Hi Solomon,

Thanks for the solution.
Previous Topic: What is Stored Procedure
Next Topic: type and rowtype
Goto Forum:
  


Current Time: Tue Apr 29 08:34:13 CDT 2025