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
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.
, 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.
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'
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.
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;
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.
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.
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].....
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'
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.
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,
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.
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:
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>
