SQL & PL/SQL :: Count Number Of Words In A String Separated By Comma
Aug 31, 2011
I need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
View 9 Replies
ADVERTISEMENT
Dec 12, 2010
I have a string like this .
'ABC-XYZ-MNO'
and i want the data in the below format
'ABC','XYZ','MNO'
View 14 Replies
View Related
Jan 22, 2009
I have a string value like -- a,,b,c,d,e,f
Using just sql, I want to put each value of the above string in a different row. So the output should be --
a
b
c
d
e
f
using procedures it would not be that great but I want to do it just using queries.
View 1 Replies
View Related
Mar 11, 2013
I am trying to split comma separated string. My table has more than 5 lacks data. I have tried the following SQL but its taking more than 5 minutes. Any Alternative solution to return data quickly ?
SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY REGEXP_SUBSTR(order_id,'[^,]+',1,LEVEL) IS NOT NULL
SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY LEVEL <= LENGTH(order_id) - LENGTH(REPLACE(order_id, ',')) + 1
View 3 Replies
View Related
Oct 23, 2013
I have one table select * from abcd;
No err-----------------------------1 rishi,rahul2 rishi,ak I want output like:
No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this:
select no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but this query is giving me output:
1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);
View 4 Replies
View Related
Aug 4, 2008
I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.
I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.
CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_str VARCHAR2(2000) DEFAULT pi_string || ',';
[code]...
View 26 Replies
View Related
Oct 19, 2012
Create table a ( Objectid number, Value varchar2(2000);
/
Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');
I should get the output as:
ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11
View 6 Replies
View Related
Jul 2, 2008
Here is what i have in ms-sql, how to convert this into t-sql ?
@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0
[Code].....
View 2 Replies
View Related
May 30, 2013
I have a few questions about querying using ranges and comma separated lists. The basic situation is a request comes in with part numbers that can be formatted in a range, comma separated lists or both. For an example, the request contains the following part numbers:
<pnum> 1-10, 14, 17, 11, 21-24 </pnum>
I can muster a basic SQL statement to query for this by hand (more then one way to do this)-
SELECT *
FROM part_table
WHERE pnum BETWEEN '1' AND '10'
OR pnum BETWEEN '21' AND '24'
OR pnum IN (14, 17, 11);
is there a way to create the BETWEEN statement so that the dash doesnt need to be parsed out of the request? (like BETWEEN '1-10') or something that functions to that extent? Is it also possible to nest the BETWEEN statements (or the functionality of the BETWEEN) in the IN statement?
View 1 Replies
View Related
Jun 19, 2010
We have a existing view which pulls list of orders against a deal. Straight forward result of the view is below.
Deal idSALES_ORDER_NUMBER
280760548460578
280760548789518
280760548794798
280760548794799
280760548873291
280760548887725
280760548900581
Each deal may have different number of orders.
We need to modify the view logic to result the list as comma seperated:
48460578,48789518,48794798,48794799,48873291,48887725,48900581
how to proceed.
View 2 Replies
View Related
Mar 3, 2003
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
View 4 Replies
View Related
Oct 30, 2012
I would like get rows into comma separated values expected output
rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:
create table test1 (name1 varchar2(10));
insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;
SQL> select * from test1;
NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO
View 3 Replies
View Related
Mar 4, 2011
SELECT 'TEST','F1,F2,F3,F4' from dual
I want to split the rows by comma separated as below
TEST F1
TEST F2
TEST F3
TEST F4
View 5 Replies
View Related
Dec 7, 2011
I have a comma separated list 'black','red','white' and I want to get each of words in this list in one rows. Some time ago I done it with:
QUOTE select * from XXX('black','red','white')
where XXX was a function which converted list to table.
View 1 Replies
View Related
May 21, 2012
I have a requirement wherein I need to enforce certain column to have only comma as a delimiter, where multiple values exists.
Example:
Create table:
create table test_oz ( slot number, server_name varchar2(50), used_by varchar2(50),
constraint test_oz_pk primary key (slot, server_name) );
Insert Test Data:
insert into test_oz values ( 1,'SRV1','SAMMY' );
insert into test_oz values ( 2,'SRV1','SAMMY,TOM' );
commit;
Data:
SQL> select * from test_oz;
SLOT SERVER_NAM USED_BY
---------- ---------- ----------
1 SRV1 SAMMY
2 SRV1 SAMMY,TOM
From above, the USED_BY column data need to be only comma separated. Is there a way to enforce that?
When user tries to insert data using any other delimiter, it should fail.
I was trying to see if a CHECK CONSTRAINT could be of use, but could not find it to work.
View 3 Replies
View Related
Dec 12, 2011
I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.
1.) The search interface must have a single box, like google.
2.) Multiple search terms will be separated by a comma.
3.) The table has the following columns:
-- Name
-- Title
-- addr
-- addr2
-- city
-- state
-- zip
-- phone
-- email
4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)
5.) Each search term will be checked against various columns.
6.) Search terms must not have a preference in order. Name, Address = Address, Name
7.) Records will be returned only for the rows where all search terms are found.
View 3 Replies
View Related
May 23, 2012
I need to write a function which will take table name as input and should return all the columns separated by coma (,).
For example I have a table product as
PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001
My function should return the output as
100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001
Is there any inbuilt function available?
View 10 Replies
View Related
Apr 24, 2013
I have the following query : for :P_LEG_NUM Parameter when i am passing values like 1,2,5 as string type i am getting invalid number error... I have defined in clause for it but still it does not work.. For individual values like 2, etc it works... how can i pass comma separated values for this bind variable
select trip_number as prl_trip_number,
flight_number as prl_f_number,
trip_leg_id as prl_trip_leg_id,
leg_number as prl_leg_num,
dicao as prl_dicao,
[code]........
View 2 Replies
View Related
Aug 2, 2007
I have a string like this:
s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';
How can I find the number of lines in this string? I tried using
INSTR('s_list', '
', 1, 1)
but it gives 0.
Is there any inbuilt function/proc SQL or PL/SQL which can do this?
View 2 Replies
View Related
Mar 1, 2012
I have a table with the following column and data is like this.
SQL>CREATE TABLE test (
column1 varchar2(50));
SQL>INSERT INTO test VALUES('ABC XYZ');
SQL>INSERT INTO test VALUES('MNO PQR');
SQL>INSERT INTO test VALUES('ABCD ABC');
SQL>INSERT INTO test VALUES('PQR MNOP');
[code]....
View 13 Replies
View Related
Sep 25, 2013
DB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
View 4 Replies
View Related
Mar 7, 2013
I have given below example.I hope that is clear.
M_STR_1 VARCHAR2(1000);
M_STR_2 VARCHAR2(1000);
BEGIN
M_STR_1 :='CHANDAN,RATTAN,PL,SQL';
M_STR_2 :='PL,SQL,RATTAN,CHANDAN,WINDOWS,LINUX';
--Output should like this same as order of M_STR_1
M_STR_2 :='CHANDAN,RATTAN,PL,SQL,WINDOWS,LINUX';
I think approach should be like take 1st word (before ,)and search the position in M_STR_1
and assign the that position in another variable using ltrim or rtrim. Well this what is i think
View 3 Replies
View Related
Oct 19, 2011
wrote a code that display the first 10 words from a string.
View 15 Replies
View Related
Apr 5, 2011
I am trying to delete duplicate or repeating words in a string using regular expressions (regex) and an Oracle database.I have Googled quite a bit on this topic and was unable to track down something that fit the bill or remotely came close to paying the tip.
A pattern would be: word word start with newThe final string should look like this: word start with new Basically all the strings begin with the two words repeating.
View 7 Replies
View Related
Jun 7, 2012
I have a scenario where I have to extract data in a string which is enclosed in [].
Here is what I want to do..
input:
[name_first] [name_mi] [name_last]
required output: this should return all the data that is enclosed in the brackets.
name_first
name_mi
name_last
I have tried the "REGEXP_SUBSTR" but my database version is 9i, and it is available only from 10g.
View 3 Replies
View Related
Jul 29, 2013
I have to convert string 1234567 as 123,456,7 .
note 1234567 is a string.
View 6 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
Aug 29, 2012
To remove the last comma end of string and load the Clob data into table. create table test(name clob)
View 2 Replies
View Related
Nov 12, 2010
I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
View 9 Replies
View Related
Oct 1, 2012
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
View 12 Replies
View Related
