Results 1 to 6 of 6

Thread: regexp_instr

  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Unanswered: regexp_instr

    Hello,

    Does DB2 has the similar function that could give a way to search within a string such as Oracle regexp_instr?

    SELECT REGEXP_INSTR ('TechOnTheNet is a great resource', 't', 1, 1, 0, 'i')
    FROM dual;

    Thanks in advance

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello,

    No, there is no similar function.
    What's your final goal?
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Hello Mark, hope you are well! Thanks for reply.

    I was trying to use regexp_instr to match the the word/pattern of word by counting the character from the first position defined and the last position of the character from the source of the string.

    Apparently, few days back I have updated my reply with the solution which I have found, I have submitted with the quick reply, the message did not log here.

    Here is the user defined function (udf) which I found that allow to work with the same behavior of regexp_instr.

    CREATE FUNCTION REGEXP_POSITION (SOURCE_STRING VARCHAR(32000)
    ,PATTERN VARCHAR(32000)
    ,SEQUENCE INTEGER)
    RETURNS INTEGER
    SPECIFIC REGEXP_POSITION_NOFLAGS
    LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
    RETURN REGEXP_POSITION(SOURCE_STRING,PATTERN,SEQUENCE,'')
    #

    /*------------------------------------------------------*/

    CREATE FUNCTION REGEXP_POSITION (SOURCE_STRING VARCHAR(32000)
    ,PATTERN VARCHAR(32000)
    ,FLAGS VARCHAR(4))
    RETURNS INTEGER
    SPECIFIC REGEXP_POSITION_NOSEQ
    LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
    RETURN REGEXP_POSITION(SOURCE_STRING,PATTERN,1,FLAGS)
    #

    /*------------------------------------------------------*/

    CREATE FUNCTION REGEXP_POSITION (SOURCE_STRING VARCHAR(32000)
    ,PATTERN VARCHAR(32000))
    RETURNS INTEGER
    SPECIFIC REGEXP_POSITION_NOSEQ_NOFLAGS
    LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
    RETURN REGEXP_POSITION(SOURCE_STRING,PATTERN,1,'')
    #


    Hope the above will help other who need this.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I have no idea where you found these functions, but I doubt that it's a good idea to implement the regular expressions with sql language.
    I believe it's much easier to reuse already implemented algorithms with java, for example.
    Capturing Groups
    So, it should be easy to implement such a function as a JAVA UDF.
    Regards,
    Mark.

  5. #5
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    I do agreed with you by using Java UDF that has the similar function as regular expression.
    The UDF functions were created by IBM.

  6. #6
    Join Date
    Mar 2006
    Location
    Bogota, Colombia
    Posts
    21
    Db2 has regular expression capabilities via xquery. You do not need to develop anything, just call the xquery from sql and that's all.
    AngocA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •