SQL & PL/SQL :: How To Extract First 3 Characters From Each Word In A String / Sentence And Separate With Underscore

Oct 22, 2013

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.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get First Word From String

Aug 22, 2013

how to get first word from string.example i have string like:-

Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some

i am expecting first word as out put like :-

jack
mark
Raj
Deniel
Some

in the same maner if i want should get two words also.

View 36 Replies View Related

PL/SQL :: Counting Particular Word In String?

Jul 17, 2012

Have a query to find the occurence of a particular word in a string using a query.

ex: str := 'a,b,a,c,d'

search_str := 'a'

=> need to get the number of times 'a' is getting repeated in the string str.

output: 2

View 9 Replies View Related

SQL & PL/SQL :: How To Eliminate And Separate String

Apr 29, 2011

In my table i have a field called swistmsg, which contains value as

:201:0001OTT11000004|:23b1:CRED|:32a1:01-01-2011|:32a2:USD|:
32a3:1000.|:33b1:USD(similarly around 100 rows)

and my requirement is

201 0001OTT11000004 23b1 CRED 32a1 01-01-2011 32a2 USD 32a3 1000 33b1 USD

and i have eliminate '.',':'&'|' (only quoted chars)from the string separate string

View 17 Replies View Related

SQL & PL/SQL :: Separate String Using REGEXP_SUBSTR

Jan 25, 2011

I can separate numbers from string (info) and the first value of the string using REGEXP_SUBSTR (see below):

with dat as (select '35263304 Alcatel One Touch 806' info from dual)
select info, REGEXP_SUBSTR ( info, '[[:digit:]]+',1 ) tac,
REGEXP_SUBSTR ( info, '[[:alpha:]]+',1) brand
from dat

But how can I get rest of the values from that string (red color) ?

I just would like to get separately like:
35263304 Alcatel One Touch 806

View 5 Replies View Related

SQL & PL/SQL :: How To Retrieve First Letter Of Every Word From String

Oct 4, 2013

I want to retrieve first letter of every word from the string.

For ex. if string is "Computer Science Engineering (New)" then I want "CSE" or if string is "Mechanical Engineering" then "ME".

how to do this..

View 18 Replies View Related

SQL & PL/SQL :: Separate String Based On Comma?

Oct 28, 2013

This I want TO separate TO different COLUMNS based ON comma.

THE RULE IS LIKE out OF total five fields FIRST 3 comma will be FIRST 3 addresses AND rest will be address4 AND LAST NUMBER should appear IN pincode field IF found.

The trouble is for reading reverse to get the number.

WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
SELECT 'The Icon, 2nd 3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
SELECT addr1 FROM address;

View 10 Replies View Related

SQL & PL/SQL :: Extract Date From String

Jan 8, 2013

I need to extract the First portion of date entered in remarks column , i have prepared a test as below.

CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....

--Required output is

LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012

View 15 Replies View Related

SQL & PL/SQL :: Extract String Between The Braces?

Sep 13, 2011

I have following column data

NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)

Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL

the output should be

abc_mode
bch_mode
cdh_mode

View 5 Replies View Related

PL/SQL :: Extract Number From String

Oct 21, 2012

I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:

SELECT  regexp_replace (regexp_replace ( REGEXp_REPLACE ('  !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 |  " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','')  FROM dual
{code}

Is there a more efficient way to get this done ?

View 4 Replies View Related

SQL & PL/SQL :: How To Get Last 4 Characters In A String

Dec 12, 2010

how to get last 4 characters in a string. But i don't know the length , for example the string is

abcdefghij

i want only ghij.

View 5 Replies View Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.

View 3 Replies View Related

PL/SQL :: Extract Values From String To Variables?

Jul 16, 2013

I need to extract values from string to variables as below.

declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or  varchar2(100);
l_s  varchar2(100);
l_ot  varchar2(100);
l_pre  varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/

But here challenge is order of alignment change dynamically. ex as below. 

str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';

 So how to extract Acknowledgement to l_a,Order requester to l_or ...

View 9 Replies View Related

SQL & PL/SQL :: Replace Characters In A String?

Apr 7, 2011

replace the first 5 commas with the character '|' in the below string:

'Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver'

I tried:

SELECT regexp_replace('Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver',
',','|',1,5) from dual

but it only replaces the 5th comma.

View 3 Replies View Related

How To Count Distinct Characters From A String

Sep 26, 2005

how to count different characters from a string ....

View 7 Replies View Related

SQL & PL/SQL :: Removal Of Special Characters In A String

Jan 2, 2013

I am looking for a SQL query which checks for special character(s) in a string and remove them if any.

for e.g: input string : 'ABCD $ & * # 123 Test'
expected output should be in upper case : 'ABCD123TEST'

we do not know the position of special characters and also what kind of special characters will come as input.

View 17 Replies View Related

SQL & PL/SQL :: Count Repeated Characters In String

Mar 9, 2011

Is there any function to count number of specific character inside string. For example :

my string is 'ABCEF AYZA'

I want to calculate how many 'A' exist in my string. result should be = 3

View 17 Replies View Related

SQL & PL/SQL :: String Function - Extract Domain From Email Address

Oct 12, 2011

I'm trying to extract the domain from email addresses like from clay500@msn.com I want to pull msn

I figured I would use the POSITION function, but it fails, even when I cut and pasted the example from the syntax guide

SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;

the guide says you get the answer 1

but I get ORA-00907: missing right parenthesis

What gives?

View 8 Replies View Related

SQL & PL/SQL :: Remove Special Characters From Input String?

Aug 5, 2010

I have a following table,

create table test1(col1 varchar2(20));

insert into test1 values('4711-3/01');

I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?

View 29 Replies View Related

SQL & PL/SQL :: How To Find Extended ASCII Characters From A String

Sep 19, 2013

I have a table like mentioned below

create table test1( test_no number, test_description varchar2(100));
insert into test1 values (1,'ABC£¥');
insert into test1 values (2,'BCD£¥');

Now I am selecting from the above table and the expected rest it should have shown is as shown below

TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABC£¥
2 BCD£¥

But instead of showing the extended ascii characters, it is showing some different characters as shown below

select * from test1;

TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABCLY
2 BCDLY

I have an requirement where i need to fetch the exact ascii characters for £ and ¥ instead of L and Y respectively.

View 1 Replies View Related

SQL & PL/SQL :: Extract Email ID From A String / Text Using Oracle Regular Expressions

Oct 14, 2013

code to extract email id (full email id) from a string/text using Oracle Regular Expressions.

Example -

"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com

View 9 Replies View Related

SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp

Jun 12, 2013

A sample query to check whether a string contains 2 upper characters and numbers are present in that character using regular_exp .

View 12 Replies View Related

PL/SQL :: Translate Function Remove Alpha Characters From String

Feb 7, 2013

I am on 11g.

I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:

SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-

I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter

VALID INPUT samples:
4
25
11F
361NG
8ABC

View 6 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

Meaning Of Sentence SRW.USER_EXIT?

Apr 9, 2013

I am developing in the Report tool.I wonder what the meaning of the sentence SRW.USER_EXIT, and arguments that are used in it.

For example,

srw.user_exit('FND FLEXIDVAL
CODE=" "
NUM=" "
APPL_SHORT_NAME=" "
DATA=" "
VALUE=" "
DISPLAY=" "
DISPLAY=" "
IDISPLAY=" "
IDISPLAY=" "');
return();

View 2 Replies View Related

Performance Tuning :: Next SQL Sentence

Jul 4, 2012

I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.

SELECT DISTINCT hash_value,
sql_text
FROM gv$sql sq
WHERE hash_value IN (SELECT DISTINCT prev_hash_value
FROM gv$session se
WHERE sid IN (SELECT sid
FROM gv$lock l
WHERE type = 'TX'
AND ctime >= 2000
AND l.inst_id = se.inst_id
AND l.sid = se.sid)
AND sq.inst_id = se.inst_id);
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Validate Sentence Syntax?

Dec 29, 2010

Is there some way to validate the syntax of sql sentence without actually executing it? I dont want to check if the objects exist or not, just want to check the syntax.

View 6 Replies View Related

SQL & PL/SQL :: How To Count Different Items With One Sentence

Nov 4, 2013

CREATE TABLE "CIM_TEST"
(
"NAME" VARCHAR2(60),
"COUNTRY" VARCHAR2(100),
"CITY" VARCHAR2(100),
"TOWN" VARCHAR2(100),

[Code]..

How to count different items with one sentence instead of above?

View 10 Replies View Related

SQL & PL/SQL :: Number Of Active Sessions With Same Sentence?

Oct 20, 2011

I have a RAC on 9i and I want to know the number of active sessions that it's executing the same sql sentence.I'm using this sql but I'm not sure that this query give me te correct result.

SELECT se.inst_id,se.status,se.SQL_HASH_VALUE,se.USERNAME,substr(sq.SQL_TEXT,1,50) SQL,count(*)
FROM GV$SESSION se, gv$sql sq
WHERE se.STATUS='ACTIVE'
and se.username is not null

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values

Jul 23, 2013

create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');

how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?

so my o/p be like;

Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved