PL/SQL :: Remove Consecutive Occurrence From String?
Jun 4, 2013
version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
i want to ,remove consecutive occurance from string
Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL'
to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method
WITH T
I am using regexp_replace function to replace the string between the double quotes with the first occurance but i am able to replace first occurance but I am not getting remaining string.
For example:
select REGEXP_replace('Parent.addChildByName("DS-Id of OAL(BROBA)")||parent.add("DS-Id of OAL(BROBA)")','["]:print:+:punct::print:*["]','XXXX') from DUAL
O/P: Parent.addChildByName(XXXX)
Expected O/P : Parent.addChildByName(XXXX)||parent.add("DS-Id of OAL(BROBA)")
SELECT country_name, substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME, substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE, substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as [code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
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?
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
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
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?
I'm trying to find a way to see if a value occurs more than once in a string. I just need to know "T/F", or "Y/N", etc.
The string will be comma delimited.
String: '1,2,3,1' Ans: "T"
String: '1,2,3,4' Ans: "N"
They do need to match exactly. for instance
String: '1,2,3,1a' Ans: "N"
Using some code I found on this site, I coded this but I'm sure there's a better way. Is there??
--Check for Duplicate combination row values BEGIN --Query will split the string into individual pieces. --Group the pieces to see if any 2 rows are the same --If no rows are the same then "no_data_found" exception is thrown SELECT 'T' INTO vResult
My need is to locate an occurrence of symbols starting from "s." (non-capital letter), following by word (with any capital letter at the beginning) and ending with ", " (comma and space symbols).
Ex:
select 'jeklghje, s.Glkgje, u.slgjwek, 904869' as tt from dual union all select 's.Tklgj, u.slgjwek, 23578, elslgjs' as tt from dual union all select 's.klgj, u.ekgjes, 238573, dlsjkgj' as tt from dual
I'm looking for occurrence of "s.Glkgje, " and "s.Tklgj, ".
I think some combination of REGEXP_INSTR and REGEXP_SUBSTR should be useful, but I'm not familiar with these functions so good yet.
'm using the "Highlight Words" column formating for a report. There, I'm using the item syntax: &P1_RENVOI. Problem is, when the report appear, only the first occurence of the value of item "P1_RENVOI" is in red for every field in that column.
Is this a normal feature or should all occurences be put in red?
I need to search a specific pattern from a source code. In word, I need to check whether "getCode" has been called or not, for all the string inside double-quote("). Following are sample code lines -
Now, expression should be such that it will return true during check for 1, 3 and 4, although, for 3 & 4 getCode has been called for part of the String.
In the example the client 123 appears from 2010/10/04 to 2010/10//08 (5 consecutive days), so this client must appear in the output. In the example customer 456 does not appear at least 4 consecutive days, so should not appear in the output.
I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.
So my desired output is:
Col1 Col2 Col3 1 A 1 2 B 1 3 C 1 4 D 1 5 D 2 6 A 2
[code]...
Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.
I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this.
i want to restrict the user if he/she enters any 3 consecutive sequence of numbers,characters,alphanumerics and special characters for example aaa, aAa, @@@, ---- , 111, 123 are not valid.
valid sequences are a1w,?1A,aa1,WW2,78a,-#a
i want to show the invalid sequence in a single query using regular expression function. suppose for example if user enters aaa,$$$,123 then the query output is aaa,$$$,123.
i have written two different queries for that but i want a single query
SELECT REGEXP_SUBSTR('EEE','([a-z])\1\1',1,1,'i') FROM DUAL; SELECT REGEXP_SUBSTR('111','([0-9])\1\1',1,1,'i') FROM DUAL; SELECT REGEXP_SUBSTR('@@@','([^-$])\1\1',1,1,'i') FROM DUAL; -it is not checking for -(hypen) characters
$x being a range of non-consecutive values like so: 1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?
SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days FROM tmsh_timesheet a INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
I want update col1 whis is null to max(col1) ++ in a row, order by cr_date like 1,1,20110102 2,2,20110101 3,null,20110105 => 3,5,20110105 because this row is after 20110103 4,3,20110104 5,null,20110103 => 5,4,20110103 because this row is before 20110105
update test_table set col1 = (select max(col1) from test_table) + rownum where col1 is null;
I have a problem with a query. I have a table employee with data as
emp_id date day working_ind 1 01-Jan-2011 Mon Y 1 02-Jan-2011 Tue Y 1 03-Jan-2011 Wed Y 1 04-Jan-2011 Thu Y 1 05-Jan-2011 Fri Y 1 06-Jan-2011 Sat N 1 07-Jan-2011 Sun N 1 09-Jan-2011 Tue Y
Sundays/ Monday/ any public holiday the working_ind will be N. If the emp is absent on one day then there will be no record entered in the table (e.g. 8th jan there is no record). Each table has only one year data.
I need to retrieve for all employees when they worked for 30 consecutive days without being absent which does not include sat/ Sunday / holidays.
Its like: -- i need to order by emp_id and date -- get oly the data with working_ind as Y -- make sure that i get 30 consecutive days (from what ever i get above) where no days data is missing
I tried using lag and inner join but it does not seem to be working.
how to query a list to see if a person had events in consectutive months within the past year. We call a person a LongTermResident if they had a review in any two consectutive months within a reporting period. I wrote a function isResidentLongTerm, passing in FacilityID, ResidentID, ReportPeriodStart, and ReportPeriodStop and returning a 'Y' or 'N'. It works, but the performance is slow.
So if I have a list of reviewers, facilities, reviewees I want to select only those SNF/NF residents who have had routine reviews in any two consectutive months at the same facility.
This is my query:
select ConsultantID, ResidentID from ( select distinct ConsultantID, FacilityID, ResidentID from Reviews where BedType = 17820 -- SNF/NF bed and ReviewType = 17474 -- routine review ) where isResidentLongTerm( FacilityID, ResidentID, :startDate, :stopDate ) = 'Y'
I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.
For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.
create table T (student_id number, class_id number, quiz_id number, marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50); INSERT INTO T VALUES (2,2, 2, 40); INSERT INTO T VALUES (3,1, 3, 34); INSERT INTO T VALUES (1,1, 4, 10); INSERT INTO T VALUES (1,1, 5, 30); INSERT INTO T VALUES (1,1, 6, 29); INSERT INTO T VALUES (3,2, 7, 34); INSERT INTO T VALUES (3,2, 8, 33); INSERT INTO T VALUES (3,2, 9, 56); INSERT INTO T VALUES (1,1, 7, 90); INSERT INTO T VALUES (2,2, 8, 0,); INSERT INTO T VALUES (1,1, 8, 80); INSERT INTO T VALUES (2,2, 8, 65); INSERT INTO T VALUES (1,1, 9, 34); INSERT INTO T VALUES (2,2, 9, 11);
each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.