Home » SQL & PL/SQL » SQL & PL/SQL » Help with regexp_substr
icon5.gif  Help with regexp_substr [message #567543] Wed, 03 October 2012 04:11 Go to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Hi, could you please help me with the following:
I have a column with strings with undefined length like:

ABC_CBA_20120929_123030_ABC.FFF
ABC_FFFFF_20120929_133030_444_ABC.FFF
ABC_hhh.I.20120928_141414_1234567_PP.FFF
ABC_MM_TT_HH_20120927_122334_P_00.FFF
ABC_MTH.PP.20120926_223344_HM.FFF

And I am using following statement to select rows between based on the dates in blue:

SELECT * 
FROM   files 
WHERE  Regexp_substr(file1, '\d+') BETWEEN '20120927' AND '20120928'


The result is:

ABC_hhh.I.20120928_141414_1234567_PP.FFF
ABC_MM_TT_HH_20120927_122334_P_00.FFF

The problem is that this is working when I manually insert the strings, using insert statement or edit data on the PL/SQL Developer.
But when the data is inserted by an application or SQL Loader it doesn't work, doesn,t not return result. The tables are the same:

CREATE TABLE log_files 
  ( 
     log_fl_nm VARCHAR2(100) 
  ) 


Does anybody have an idea what can be the reason?
Or another way of doing that?

This is on Oracle 11.2
Re: Help with regexp_substr [message #567544 is a reply to message #567543] Wed, 03 October 2012 04:16 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Can you post me what error you are getting?
But when the data is inserted by an application or SQL Loader it doesn't work, doesn,t not return result.
Re: Help with regexp_substr [message #567545 is a reply to message #567543] Wed, 03 October 2012 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does anybody have an idea what can be the reason?


You are doing something wrong or there is a bug.
Posting what is working but not what is not working does not help in any way to know why it is not working.

Regards
Michel

[Updated on: Wed, 03 October 2012 04:18]

Report message to a moderator

Re: Help with regexp_substr [message #567549 is a reply to message #567545] Wed, 03 October 2012 04:39 Go to previous messageGo to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
There is no error, it just does not return any result.
Re: Help with regexp_substr [message #567553 is a reply to message #567549] Wed, 03 October 2012 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So

Quote:
You are doing something wrong or there is a bug.


Regards
Michel
Re: Help with regexp_substr [message #567554 is a reply to message #567553] Wed, 03 October 2012 04:47 Go to previous messageGo to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Yes, seems like a bug, maybe something in the database, because I just tried with another database and it is working fine.
Thanks for the help!
Re: Help with regexp_substr [message #567555 is a reply to message #567553] Wed, 03 October 2012 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Query you posted selects from "FILES", while CREATE TABLE suggests that it is "LOG_FILES". So, which one is true? Possibly both, but then you need to make sure that - after loading session - you select from the right table.
Re: Help with regexp_substr [message #567595 is a reply to message #567555] Wed, 03 October 2012 07:56 Go to previous messageGo to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Yes, LOG_FILES and FILES, typing mistake, I do select from the right table. I just found something:
when strings are like
ABC_MM_TT_HH_20120927_122334_P_00.FFF - under score in front of the date it works
when they are like
ABC_hhh.I.20120928_141414_1234567_PP.FFF - dot in front of the date it doesn't work

Is there any way to handle this?

Thanks in advance!
Re: Help with regexp_substr [message #567618 is a reply to message #567595] Wed, 03 October 2012 09:23 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Just now i tried it's working with this sql

SELECT * FROM   log_files WHERE  Regexp_substr(LOG_FL_NM, '\d+') BETWEEN '20120927' AND '20120928' 
Re: Help with regexp_substr [message #567620 is a reply to message #567618] Wed, 03 October 2012 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bravo! This is what OP said in his question.

Regards
Michel
Re: Help with regexp_substr [message #567627 is a reply to message #567620] Wed, 03 October 2012 11:27 Go to previous messageGo to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
OK, I tripple check and:
It works for these strings
LUXTG_MICCNSN_LUXTG_20120927_002002_008596_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_005003_008597_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_012003_008598_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_015003_008599_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_022003_008600_00.MBF

And it doesn't work for these strings
ICT2N_CMS.27.AMSA.I.20120927_001455_P_03074_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_002956_P_03075_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_004457_P_03076_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_005958_P_03077_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_011459_P_03078_00.MBF


The only thing that I can see are the dots before the date and in the working way there are no dots before.
Very strange?? May be it is something on my side.... Any ideas?

Thanks in advance!
Re: Help with regexp_substr [message #567628 is a reply to message #567627] Wed, 03 October 2012 12:26 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
Try this


REGEXP_SUBSTR(strval, '(\d{8}){1,1}') BETWEEN '20120927' AND '20120928';


This will work if we suppose that only date contains 8 successive digits in each line.



WITH logfiles AS
(
SELECT 'LUXTG_MICCNSN_LUXTG_20120927_002002_008596_00.MBF' AS strval FROM DUAL UNION
SELECT 'LUXTG_MICCNSN_LUXTG_20120927_005003_008597_00.MBF' AS strval FROM DUAL UNION
SELECT 'LUXTG_MICCNSN_LUXTG_20120927_012003_008598_00.MBF' AS strval FROM DUAL UNION
SELECT 'LUXTG_MICCNSN_LUXTG_20120927_015003_008599_00.MBF' AS strval FROM DUAL UNION
SELECT 'LUXTG_MICCNSN_LUXTG_20120927_022003_008600_00.MBF' AS strval FROM DUAL UNION
SELECT 'ICT2N_CMS.27.AMSA.I.20120927_001455_P_03074_00.MBF' AS strval FROM DUAL UNION
SELECT 'ICT2N_CMS.27.AMSA.I.20120927_002956_P_03075_00.MBF' AS strval FROM DUAL UNION
SELECT 'ICT2N_CMS.27.AMSA.I.20120927_004457_P_03076_00.MBF' AS strval FROM DUAL UNION
SELECT 'ICT2N_CMS.27.AMSA.I.20120927_005958_P_03077_00.MBF' AS strval FROM DUAL UNION
SELECT 'ICT2N_CMS.27.AMSA.I.20120927_011459_P_03078_00.MBF' AS strval FROM DUAL UNION
SELECT 'ICT2N_CMS.27.AMSA.I.20120929_011459_P_03078_00.MBF' AS strval FROM DUAL
)
SELECT strval
FROM logfiles
WHERE  REGEXP_SUBSTR(strval, '(\d{8}){1,1}') BETWEEN '20120927' AND '20120928';



STRVAL
--------------------------------------------------
ICT2N_CMS.27.AMSA.I.20120927_001455_P_03074_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_002956_P_03075_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_004457_P_03076_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_005958_P_03077_00.MBF
ICT2N_CMS.27.AMSA.I.20120927_011459_P_03078_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_002002_008596_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_005003_008597_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_012003_008598_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_015003_008599_00.MBF
LUXTG_MICCNSN_LUXTG_20120927_022003_008600_00.MBF

10 rows selected.

SQL>



Regards,
Dariyoosh

[Updated on: Wed, 03 October 2012 12:33]

Report message to a moderator

Re: Help with regexp_substr [message #567630 is a reply to message #567628] Wed, 03 October 2012 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As OP said, once again, his formula works with SQL*Plus, TOAD, PL/SQL Developer or whatever but does not with his application (or SQL*Loader). I think the point to investigate is this one, not the formula.

@apenkov

Post CREATE TABLE, SQL*Loader control file, attach a file with the data that do not work.
Also post the NLS_LANG you use both with PL/SQL Developer and SQL*Loader as well as the character set of your database.

Regards
Michel
Re: Help with regexp_substr [message #567678 is a reply to message #567630] Thu, 04 October 2012 04:29 Go to previous messageGo to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Hi,

@Dariyoosh

Thanks a lot man, it works!

@Michel

I am just curious why the first one doesn't work?? Here are the info:
-- Create table
create table LOG_FILES
(
  LOG_FL_NM VARCHAR2(100)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );


Control file:
OPTIONS ( ERRORS=999, ROWS=1000, DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA

APPEND
INTO TABLE "MACHATPE"."LOG_FILES"
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(LOG_FL_NM)


Regarding the NLS_LANG:
For SQL*Loader and the database it is AL32UTF8
For the PL/SQL was WE8MSWIN1252
I change the character set for PL/SQL directly from the regedit, but it still doesn't work.
Attached is the list with strings for which it works and ones it doesn't.

Best regards,
Atanas
  • Attachment: example2.txt
    (Size: 0.52KB, Downloaded 59 times)
Re: Help with regexp_substr [message #567680 is a reply to message #567678] Thu, 04 October 2012 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The client NLS_LANG setting must be the same than the character set of the file you want to load otherwise you have a character set mismatch (or you specify the file character set in the control file).

I don't see the use of your regexp formula in SQL*Loader control file so how could you say it does not work?

Regards
Michel
Re: Help with regexp_substr [message #567699 is a reply to message #567680] Thu, 04 October 2012 07:04 Go to previous message
apenkov
Messages: 15
Registered: October 2012
Junior Member
OK, in order to continue this discussion I need to explain the whole process, which is long and pretty much complicated. As I have a working solution and in order not to waste anybody's time I think I don't need to go further at this stage.

@Michel

Thank you! You gave me a good point. I will make more investigations.

Thanks to everybody for the help!

Best regards,
Atanas
Previous Topic: Extracting data from XML file problem
Next Topic: SQL query help required
Goto Forum:
  


Current Time: Sun Apr 20 17:24:10 CDT 2014

Total time taken to generate the page: 0.08214 seconds