Doing a numeric year check within a string

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Doing a numeric year check within a string

Postby mode09 » Wed May 08, 2013 7:43 pm

Hello, I am trying to pull out a year from a varchar2 field in a table. It's not the most ideal table/record design, but it is unfortunately what I have to work with for this extract.

Any record >= 2014 I want to pick up. There are null records.

The year may appear anywhere in this field, and may not appear at all.
For example, data may look like
'FALL 2014',
'2014 CODE-B',
'CODE-A 2014 CODE-D',
'ADSHLHSALK' ( NON YEAR, DONT PICK UP),
'FALL 2004', (PAST, DON'T PICK UP)

I tried this, and it does not work, any ideas? I end up picking up years earlier than 2014 for some odd reason. If I try to add to_number I get a invalid number error from the records with non year data.

AND ((SUBSTR (q.season, (INSTR (q.season, '201')), '4') >= '2014') and q.season is not null) )


Is there a better approach?
mode09
Member
 
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

Re: Doing a numeric year check within a string

Postby Tim... » Wed May 08, 2013 8:20 pm

Hi.

Looks like a job for regular expressions to me!

Your data:

Code: Select all
CREATE TABLE t1 (
  data VARCHAR2(4000)
);

INSERT ALL
  INTO t1 VALUES ('FALL 2014')
  INTO t1 VALUES ('2014 CODE-B')
  INTO t1 VALUES ('CODE-A 2014 CODE-D')
  INTO t1 VALUES ('ADSHLHSALK')
  INTO t1 VALUES ('FALL 2004')
SELECT * FROM dual;

COMMIT;


The query:

Code: Select all
SELECT *
FROM   t1
WHERE  TO_NUMBER(REGEXP_SUBSTR(data, '\d{4}')) >= 2014;

DATA
----------------------------------------------------------------------------------------------------
FALL 2014
2014 CODE-B
CODE-A 2014 CODE-D

3 rows selected.

SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Doing a numeric year check within a string

Postby mode09 » Thu May 09, 2013 12:37 pm

Thanks Tim, this worked perfect!
mode09
Member
 
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

Re: Doing a numeric year check within a string

Postby Tim... » Thu May 09, 2013 1:05 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests