This forum is currently locked. You can't register or post questions at this time. (read more)

Doing a numeric year check within a string

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

mode09
Member
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

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?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

mode09
Member
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

Re: Doing a numeric year check within a string

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

Thanks Tim, this worked perfect!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests