Creating query expressions using regex

[jael91407431]

Hi,

If I want to query my database with a list of values, such as db Field that contains the values: 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, and 1010...etc. all the way up to 1100.

Instead of listing them individually, "1001, 1002, 1003, 1004, 1005, 1006, 1007...etc is there a way of using regex expressions to shorten these list of values (" 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010 all the way up to 1100") inside the query?

In other words, I don't want to list these values individually, since they are numerous, but rather, wish to shorten it through an expression. Can I use regex for that?

I am thinking "1001 - 1100" (1001 to 1100 in literal terms). Is this correct?

Thank you.

[Jean-Serge_Biro]

Hello Jael,

Please might you explain why you intend to use regex for SQL queries instead standard SQL possibilities?

Usually regex is used in script languages (Javascript, JSSP for Adobe Campaign).

In SQL depending on field type you have these choices:

1. [numeric field] between operator in where clause, with query activity it doesn't exist so you must replace it by 2 clauses, one for superior or equal and the other one for inferior or equal; or if your query is not made through Query activity but by Javascript activity code, you can use between in direct SQL expressions or queryDef (or 2 clauses as for Query activity of course).

2. [varchar field] like operator with % placeholder sign.

If you must use regex for a reason or another, because Adobe Campaign Javascript support for regex is standard one, please refer to all public resources on regex on the web, and if needed support of regex in Javascript (mainly the same as in other languages).

Regards.
J-Serge

[jael91407431]

Thank you again Jean-Serge!! Again, sorry for the late response as my Outlook filtered AC forum replies into the spam box for some reason. Will implement your suggestion! Very good to know that AC uses a JavaScript regex engine!

Thank you so much!

Jae

[jael91407431]

Hi Jean-Serge,

Thank you for the reply.

So if I want to exclude, for example, the following zip codes from my segment:

EXCLUDE:

      "ZIP_CODE IN (

              48201, 48202, 48203, 48204,
48205, 48206, 48207, 48208, 48209, 48210, 48211,

              48212, 48213, 48214, 48215,
48216, 48217, 48218, 48219, 48220, 48221, 48222,

              48223, 48224, 48225, 48226,
48227, 48228, 48229, 48230, 48231, 48232, 48233,

              48234, 48235, 48236, 48237,
48238, 48239, 48240, 48242, 48243, 48244, 48255,

              48260, 48264, 48265, 48266,
48267, 48268, 48269, 48272, 48275, 48277, 48278,

              48279, 48288);'

...might'n I use regex expressions to create the expression, rather than typing each individual zip code as the values into the filtering condition (in the Query activity)?

If not, how would I go about creating such a long list of values in the query?

Jae