SQL & PL/SQL :: Finding Date Value In Given String By Using Regular Expression
Jul 3, 2012
I have a Identifier column with start and stop dates along with description .
Two dates are separated by '-'. But the position of that character(-) is not constant always. Depending on the instr function I am able to divide the start and stop dates. But I am getting the performance problem because of huge data
I think the same logic will be implemented by regular expression also . How to write the equivalent logic by using regular expressions
create table REG_EXP_TEST
(
TRANS_ID NUMBER(10),
TRANS_IDENTIFIER VARCHAR2(250)
) ;
insert into REG_EXP_TEST
(TRANS_ID, TRANS_IDENTIFIER)
values
[code].......
View 3 Replies
ADVERTISEMENT
Apr 13, 2013
What is the use of Regular Expression?
View 1 Replies
View Related
Jan 21, 2011
I have a following table,
CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......
I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z
How do i use Substr and Regular expression here ?
View 11 Replies
View Related
Jun 6, 2012
Sample Data:
SELECT 'A/BCD/CCE/DFFFF' test from dual
Expected Output:
SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;
View 6 Replies
View Related
Nov 8, 2012
I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.
select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');
Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.
search words : one two
one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.
View 7 Replies
View Related
Apr 9, 2013
Can we use regular expression to eliminate all characters other than -
A to Z
0 to 9
Special characters like - &,%,~,@,#,$,^,*,_,+,=,,/,<,>
Example
String - TEST@#_~````}{!!!12311HELLO
Expected result - TEST@#_~12311HELLO
how we can use regular expression to achieve this result.
View 4 Replies
View Related
Jul 24, 2012
Regular Expression in oracle??Especially the use of this in real time scenario.
View 4 Replies
View Related
Oct 16, 2012
Is there a way to perform a pattern check on a value ?
For example: 654321HD9
The pattern is 6 numbers, followed by 2 letters, followed by 1 number. The data type for the attribute is a string.
Examples of right or wrong
654321HD9 - correct value
654321HD - wrong value
654321111 - wrong value
HD1111111 - wong value
The pattern has to be as i mentioned above (6 number, 2 letters, 1 number) otherwise its wrong. My pattern does not cover all cases.
select REGEXP_SUBSTR('654321HD9', '[0-9]+[A-Za-z]+[0-9]+') from dual;
View 6 Replies
View Related
Nov 6, 2013
, My HTML string is like below. select '<CityName>RICHMOND</CityName> <StateCd>ABCD CDE <StateCd/><CtryCd>CAN</CtryCd><CtrySubDivCd>BC</CtrySubDivCd>' Str from dual Desired Output is<CityName>RICHMOND</CityName><StateCd>ABCD CDE <StateCd/><CtryCd>CAN</CtryCd><CtrySubDivCd>BC</CtrySubDivCd> i.e.
want to remove those spaces from tag value area having only spaces otherwise leave as it is.implement the same using Regular expression.
View 2 Replies
View Related
Oct 14, 2013
I would like to write a select that would return all places in DB that are commiting transaction.
E.g. package for testing:
CREATE OR REPLACE PACKAGE test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB ;
END TEST_COMMIT;--not a match
CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT AS
[code]....
The select should return 4 rows with --ok.
SELECT *
FROM ALL_SOURCE ASO
WHERE
REGEXP_LIKE(ASO.TEXT,'commit(s*);','i' )
AND name = 'TEST_COMMIT'
View 18 Replies
View Related
Nov 18, 2013
I would like to use the REGEX_LIKE to check a number with up to two digits and at least one decimal point: Ex.10.11.1112 This is what I have so far.
if regexp_like(v_expr, '^(d{0,2})+(.[0-9]{1})?$') t
View 3 Replies
View Related
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
Oct 6, 2011
I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....
The data will be available like this only, The same XML tag is used everywhere.
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
Mar 28, 2012
I tried this query .. but no luck / I know it has some errors
SQL> select invoice_num from ap_invoices_all where rownum < 10 and invoice_num in ('%1%','%2%');
no rows selected
View 9 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
Jul 31, 2012
I've the following data set
with t as
(
select 1 cor_id , 'SR' rt_cd,TO_Date( '05/12/2010 01:12:19 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 2 cor_id , 'SL' rt_cd,TO_Date( '05/12/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 3 cor_id , 'SR' rt_cd,TO_Date( '04/10/2010 02:11:15 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 4 cor_id , 'SL' rt_cd,TO_Date( '04/10/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual
)
select t1.*
from t t1for each iss_id =100 ,
there could me more than one record in the above data set, The requirement is i need to get the records where the trunc(dt) is maximum
I need to get 2 records with cor_id= 1 and 2.
how can i get those records
View 3 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
[Code]...
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Oct 31, 2013
I want to convert the follow string to date: 2013-12-04 11:35:54.89
View 7 Replies
View Related
Apr 15, 2007
I have a date saved in varchar2 colomn.
how can I convert the vaules into date, so I can use the date to compare data.
Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.
how can I take from this value the date '20-03-2007' as date format?
View 6 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
Jun 25, 2012
I recently became involved with databases, and i've came across with a little obstacle. I have strings that represent a date, they are very oddly formatted and need to store them as dates. the string format looks like this: 'Monday, May the 13th of 2001'
How can i effectively convert them to DATE type?
View 7 Replies
View Related
Jul 29, 2013
In our mainframe file we have date coming as format June 1,2013 . How to read and convert it in 01/06/2013 format to load in oracle table?
View 11 Replies
View Related
Jun 12, 2011
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
View 3 Replies
View Related
Nov 12, 2012
basically I have to insert in a date field a string that is a year(four characters).
The problem is that I have to convert a string in date obtaining just a year. How can i do this?
View 14 Replies
View Related
Nov 23, 2010
We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX
from
(
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX
from
(
select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
[code].....
View 4 Replies
View Related
Apr 22, 2013
v: Apex 4.2
I have Dynamic report and column say some_date column and data can be in the columun (01-JAN-13) OR (01-JAN-13,04-JAN-13), (01-JAN-13,04-JAN-13,10-JAN-13) so on.
in actual table column some_date is date field but in report as above (string) because using RTRIM(some_date, ',') some_date within the select MODEL clause.
my problem is when run the report and select filter from action menu then it doesn't give option such as < , > or so on because of string but I want it should give <, <=, >, >= operators too as date column.
View 5 Replies
View Related
Nov 29, 2007
When I try to update a date column with this:
UPDATE
event_request
SET
REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi')
WHERE
eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
$date = '2007-12-25 08:35'; INSERT INTO (REQ_EVENT_DATE) VALUES (to_date('$date', 'yyyy-mm-dd hh24:mi'));
So - what is wrong with the UPDATE query?
View 1 Replies
View Related
Dec 22, 2010
ved>create table test900 ( a number, b number);
Table created.
ved>insert into test900 values( 9,'');
1 row created.
ved>insert into test900 values( 10,null );
1 row created.
ved>select * from test900;
A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?
View 9 Replies
View Related
Jun 29, 2011
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>
View 11 Replies
View Related
