mercredi 15 juin 2016

Regex in query not working for application, but in SQL developer and unit test

I have a strange behaviour for the following query containing a regular expression:

SELECT COALESCE(lang.TITLE, ids.message_id) AS TEXT,
  ids.message_id
FROM
  (SELECT
    CASE
      WHEN regexp_instr(messages.NR, '[a-z]{2}[[:space:],_-]d+[-_]d{2,6}') > 0
      THEN regexp_substr(messages.NR, 'd+')
      ELSE messages.NR
    END AS message_id
  FROM
    ( SELECT 'GB 28647854-04' AS NR FROM dual
    UNION
    SELECT 'GB 5310031-05' AS NR FROM dual
    UNION
    SELECT '9184' AS NR FROM dual
    ) messages
  ) ids,
  LOCAL_TITLES lang  
WHERE ids.message_id = '' || lang.NUMBER_NO(+);

The LOCAL_TITLES contains the following entries:

  • 5310031 | Some localized Text
  • 9184 | Another Text

So the expected query result should be:

  • 28647854 | 28647854
  • Some localized Text | 5310031
  • Another Text | 9184

This works well, when the query runs via SQL Developer. Also I have a (Unit-/Integration-) Test for my DAO, which runs this query returning the expected result.

My problem: when the query is executed by the running web application, then the regex does not find the numeric id. Instead, the actual query result is

  • GB 28647854-04 | GB 28647854-04
  • GB 5310031-05 | GB 5310031-05
  • Another Text | 9184

Do you have an idea, why the regular expression behaves differently, when it's coming from the web application?

Aucun commentaire:

Enregistrer un commentaire