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

[Code]....

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Replace With The First Occurrence In The String

Nov 12, 2012

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)")

View 4 Replies View Related

SQL & PL/SQL :: Remove String Duplicates

Oct 11, 2013

Removing duplicates from a string that contains years and "-".

Example: 1988-1997-2000-2013-1998-1965-1997-1899

I know this can be done in regular expressions but have no experience in this subject.

select REGEXP_REPLACE(.....) from dual;

View 16 Replies View Related

SQL & PL/SQL :: To Remove Duplicates From Concatenate String

May 12, 2011

I have a query like

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.

View 2 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

PL/SQL :: String Conversion - Remove Single Codes?

May 22, 2013

I am getting string from my tool like this.... .. ‘ ‘PH1234’,’Ph3456’,’PH5678’ ‘

I wanted to remove single codes and take the each value and I have to process. Need output like this....

PH1234,
Ph3456,
PH5678.

View 4 Replies View Related

SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String

Oct 29, 2012

I want to remove more than one space from a string by excluding double quotes.

For example:

I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")

O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")

View 17 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 :: Remove Last Comma End Of String And Load Clob Data Into Table

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

PL/SQL :: Remove Duplicate Values From Concatenated Long String Of State Codes

Dec 4, 2012

Database version: 11.2.0.3.0

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).

View 4 Replies View Related

SQL & PL/SQL :: Remove String Before And After Pipeline To Get String Between Pipeline?

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

SQL & PL/SQL :: Number Of Occurrence

Oct 29, 2010

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

[Code]...

View 10 Replies View Related

SQL & PL/SQL :: Locate Occurrence Of Symbols Combination

Aug 5, 2011

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.

View 7 Replies View Related

PL/SQL :: Ordered Number As Occurrence Changed

May 30, 2013

I have following data

WITH t AS
(
select 1 rn, 'A' val from dual UNION ALL
select 2 rn, 'A' val from dual UNION ALL
select 3 rn, 'B' val from dual UNION ALL

[Code]....

I need to calculate a new column "orderedRn" as

Rn  Val OrderedRn
------------
1       A   1
2       A   1
3       B   2
4       C   3
5       C   3
6       C   3
7       A   4
8       A   4
9       B   5
10     B   5

View 4 Replies View Related

PL/SQL :: Oracle Sql Get Data After First Occurrence Of Hyphen

May 29, 2013

I need oracle sql get data after first occurrence of hyphen.

source string:
abcd - efgh

I want everything after the "-"

View 17 Replies View Related

Application Express :: Highlight Words - Only First Occurrence?

Feb 14, 2013

'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?

View 0 Replies View Related

JDeveloper, Java & XML :: Regular Expression For Exact Word And Occurrence

Mar 15, 2012

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 -

1.->if(val==23){ month_desc = "a sample data"; }
2.->if(val==23){ month_desc = getCode("a sample data"); }
3.->if(val==23){ month_desc = "a " + getCode("sample data"); }
4.->if(val==23){ month_desc = getCode("sample data"); var2="sample data2";}

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.

View 2 Replies View Related

SQL & PL/SQL :: Get Consecutive Days?

Oct 18, 2010

I need to know if a customer appears at least 4 consecutive days. Here's an example of data

CLI_ID , DATE
-------------------------
123 , 2010/10/01
123 , 2010/10/04
123 , 2010/10/05
123 , 2010/10/06
123 , 2010/10/07
123 , 2010/10/08
123 , 2010/10/10

456 , 2010/10/01
456 , 2010/10/02
456 , 2010/10/03
456 , 2010/10/06
456 , 2010/10/07
456 , 2010/10/08
456 , 2010/10/11

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.

View 12 Replies View Related

SQL & PL/SQL :: Checking Consecutive Numbers

May 11, 2012

I have a table with three columns: terminal, place and batch. How can I check for missing batches?

select * from transactions;

terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825

View 5 Replies View Related

SQL & PL/SQL :: Ranking Non-Consecutive Rows?

Aug 23, 2012

I have a table T with columns

Col1 Col2
1 A
2 B
3 C
4 D

[code]..

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.

View 3 Replies View Related

SQL & PL/SQL :: Find Two Or More Consecutive Dates?

Feb 23, 2010

I am pulling information from a view to get the current results and placing them into a cursor which then inserts the results into a temp table.

Current Results:
JOB_NAMESCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00Failed
JOB_1 02/22/2010 13:25:00Failed
JOB_1 02/21/2010 13:25:00Failed
JOB_1 02/19/2010 13:25:00Failed
JOB_2 02/21/2010 13:25:00Failed
JOB_2 02/18/2010 13:25:00Failed
JOB_2 02/17/2010 13:25:00Error
JOB_2 02/15/2010 13:25:00Error

Needed Results:
JOB_NAMESCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00Failed
JOB_1 02/22/2010 13:25:00Failed
JOB_1 02/21/2010 13:25:00Failed
JOB_2 02/18/2010 13:25:00Failed
JOB_2 02/17/2010 13:25:00Error

View 5 Replies View Related

Restricting Entering 3 Consecutive Sequence?

Nov 14, 2010

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

any 3 consecutive characters from key board.

View 2 Replies View Related

Querying Large Non-consecutive Range

Aug 23, 2007

SELECT * FROM table WHERE id = $x

$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?

View 3 Replies View Related

SQL & PL/SQL :: Consecutive Dates - Select Distinct

Mar 23, 2010

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

[Code]...

this query results

EMP_IDCAL_IDTSDATE

048283404/10/2010
048283502/11/2010
048283503/11/2010
048283504/11/2010
048283508/11/2010

i need the ts date to be in like this

04/10/2010
02/11/2010 - 04/11/2010
08/11/2010

View 16 Replies View Related

SQL & PL/SQL :: Update Column To Consecutive Number?

Apr 14, 2011

create table test_table(
rn varchar2(10),
col1 varchar2(10),
col2 varchar2(10),

[code]...

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;

this gives ora-00933

View 6 Replies View Related

SQL & PL/SQL :: Data Count - Largest Consecutive Series Of Value

Oct 23, 2011

provide SQL query (not PL/SQL block) for the following. I have data in table as shown below

Serial No value
========= ========
123
2-99
3-99
4-99
534
656
77
88
990
1065
1167
12-99
13-99
14-99
15-99
16-99
17-99
182
1945
206

The output of the SQL will be the largest consecutive series of the value -99 is 6 and total count of the value -99 is 9

View 21 Replies View Related

SQL & PL/SQL :: Query To Get Consecutive Working Days From A Table

Jul 15, 2011

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.

View 5 Replies View Related

SQL & PL/SQL :: List Persons Having Event In Consecutive Months?

Apr 7, 2011

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'

View 1 Replies View Related

SQL & PL/SQL :: Ranking Of Consecutive Non-increasing Values In A Column?

Apr 14, 2012

I have a table with numbers like this in a column

sr no. value
1 17
2 0
3 0
4 38
5 32
6 24
7 12
8 51
9 42
10 1
11 1
12 0
13 0
14 1
15 1
.
.
.
so on....

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.

my desired output is:

sr no. value Rank
1 17 1
2 0 1
3 0 1
4 38 2
5 32 2
6 24 2
7 12 2
8 51 3
9 42 3
10 1 3
11 1 3
12 0 3
13 0 3
14 1 4
15 1 4
.
.
.

View 3 Replies View Related

SQL & PL/SQL :: All Selected N Consecutive Rows Retrieved In Single Query

Nov 11, 2011

I have table T with 50,000 rows

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..

SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)

[Code]....

SQL> /

QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11

15 rows selected.

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...

my sample output is:

Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?

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.

View 7 Replies View Related







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