SQL & PL/SQL :: String Together Number Of LIKEs With ORs
Jun 28, 2010
If I have a "smart" key in a database, say one for which each byte of the key is meaningful, is it generally a good idea to string together a number of LIKEs with ORs? For example, if I want to select people where the last two bytes of that smart code are in a certain list and write:
where smart_key LIKE '%02'
or smart_key LIKE '%03'
or smart_key LIKE '%07'
or smart_key LIKE '%19'
or smart_key LIKE '%23'
or smart_key LIKE '%30'
or smart_key LIKE '%33'
or smart_key LIKE '%34'
or smart_key LIKE '%41'
etc.
Say I string a lot of those together, with 40 or 50 ORs. Right off the bat is it fair to say that's a bad query for Oracle, or do substrings of this sort generally perform well?
View 15 Replies
ADVERTISEMENT
Jun 17, 2010
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
View 7 Replies
View Related
Oct 20, 2012
I can't understand >>
1. Number 2 is less than number 100,
2. string '2' is greater than string '100'.
>> Numbers are ordered by numerical value; strings are ordered by alphabetical value. >>
View 1 Replies
View Related
Apr 27, 2012
I found this query in my sub version repository and really wondering how this working
select to_char(to_date(1000000,'J') ,'JSP') string_value from dual;
View 1 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
Jul 9, 2012
Im trying to generate this link on my result [URL].....
materialId and materialFolderId are the results of my query.. Im not sure how to combine my results and string on my query..
View 14 Replies
View Related
Apr 24, 2012
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
View 13 Replies
View Related
Dec 6, 2010
I wanna convert the amount of money from number to string such as 144.5 to be one hundred forty four point five is there any function or i have to write my function? How could i put new line in the string?
for example if i have 'SAB Bank' || 'Riyadh'
but i want SAB bank to be displayed in line and Riyadh in line.
View 2 Replies
View Related
Oct 21, 2012
I am confused with third one.
CODE1. >> Comparing two strings >>
SQL> select greatest('99' ,'100') from dual;
GR
99
2. >> comparing both numbers >>
SQL> select greatest( 99 ,100) from dual;
GR
100
3.>> Comparing string and number >>
SQL> select greatest('99' ,100) from dual;
GR
99
whats the logic behind 99 being returned for thirdone.
View 3 Replies
View Related
Feb 4, 2012
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
SQL>
View 13 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
Aug 6, 2010
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
View 10 Replies
View Related
Nov 5, 2011
I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:
#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0
Here, I want to update the above field value to a value in the form:
#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0
This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).
View 4 Replies
View Related
Oct 4, 2011
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
View 7 Replies
View Related
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
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
Mar 14, 2013
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
View 3 Replies
View Related
May 9, 2008
show an ex to use string buffer for select statemnt
View 1 Replies
View Related
Jan 26, 2010
i am using oracle developer 6i report builder i required this type of query
example
if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;
return (TRUE);
end;
but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.
View 34 Replies
View Related
Oct 21, 2011
I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting
For example i/p asdfasfsdS251432dasdasd o/p should be S251432
View 10 Replies
View Related
Oct 3, 2013
I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?
View 1 Replies
View Related
May 21, 2010
how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies
View Related
Mar 9, 2011
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
View 4 Replies
View Related
Sep 28, 2012
my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?
View 6 Replies
View Related
Feb 19, 2011
Here is script of tables
Quote:drop table p;
create table p (qty number(3), beg_no number(5));
insert into p values(5, 110);
insert into p values(8, 786);
drop table s;
create table s (used_no number(5));
insert into s values(111);
insert into s values(113);
insert into s values(791);
Table p: it has ticket quantity and ticket begining number. Thus according to first record ticket number will begin at 110 and will end at 110+5 (Beg_no +qty). According to second record ticket number will begin at 786 and will end at 786+8 (Beg_no +qty). This table can have many records.
Table s: it has ticket numbers which are sold. The ticket will always be any number from table and will lay in any record in this format between beg_no and beg_no+qty
Required: I want "p MINUS s" information. i.e.
Quote:
110
112
114
786
787
788
789
790
792
793
I am not expert in level by command.
Oracle version: 9
OS: Windows XP
View 3 Replies
View Related
Feb 19, 2013
I have a report created in Reports6i. I have two fields at the bottom
1.Page Total
2.Voucher Total
I do not want to print the Page Total if the total number of pages = 1.
How can I achieve this?
View 1 Replies
View Related
May 10, 2010
I have a string like below:
string = 'HEADER||MEAL||15'
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
View 9 Replies
View Related
May 22, 2009
I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.
View 1 Replies
View Related
Oct 12, 2007
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
View 6 Replies
View Related
Oct 9, 2008
say there is astring "mumbojumbo "i need the count of given string in it
ex:when o is given count shuld be 2 when m is given count shuld be 3
is there any pre defined function for counting a given string ...
View 2 Replies
View Related
