Re: [EXT] Re: How can I detect an invalid date string in SQL?

Just a caveat here about the regexp functions in SQL, they require
International Components for Unicode to be installed. I have not come
across a company where it was installed before I got there. (Yes, small
sample size, but I have not been to any company where one of you here were
there first :) Fortunately for me, they were willing to install it here.
Point is the REGEX functions are not available by default, and many folks
don't install the requisite LP even though it's free.

On Wed, Apr 17, 2019 at 11:45 AM Salsman, Michael <
Michael.Salsman@xxxxxxxxxxxxxx> wrote:

John,

That works and is much simpler to type. I forgot about the character set
grouping not being necessary.

select storagedt, CASE WHEN
REGEXP_LIKE(trim(storagedt),'\d{4}-\d{2}-\d{2}') then
dec(date(storagedt)) else 0 end as "as Decimal" from mytable

Michael Salsman

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of John Yeung
Sent: Wednesday, April 17, 2019 09:29
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXT] Re: How can I detect an invalid date string in SQL?

On Wed, Apr 17, 2019 at 11:10 AM Salsman, Michael <
Michael.Salsman@xxxxxxxxxxxxxx> wrote:

This worked for me. Not the most flexible but it works and can be

altered in the future for you.

select storagedt, CASE WHEN
REGEXP_LIKE(trim(TEST),'[\d][\d][\d][\d][-][\d][\d][-][\d][\d]') then
dec(date(storagedt)) else 0 end as "as Decimal" from mytable

You don't need to put every character being matched in a set. So you could
do '\d\d\d\d-\d\d-\d\d' instead.

Moreover, the regular expressions in Db2 for i (not sure about other
databases) support counting, so you can actually do '\d{4}-\d{2}-\d{2}'.
This is quite standard among regular expression processors.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=jxkTHpqO1H1YZi_-IMcrcLerhvihD9QLMW2-h3jhFwg&s=aMjsrOb2llyQIVrgpx7SgpHUptwI25r1HyGa49ocQEo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=jxkTHpqO1H1YZi_-IMcrcLerhvihD9QLMW2-h3jhFwg&s=kJ_gBeVNCx95S1RApZ-fCHfZm6b-xRQjx_L4YQSWaJc&e=
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=jxkTHpqO1H1YZi_-IMcrcLerhvihD9QLMW2-h3jhFwg&s=ox4f4RUqsq-VFI1Z5esxo9wTVatmIoeK1yA1GkuKeBE&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com