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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 26, 2005
how to count different characters from a string ....
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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