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

For V7R3 ...

5770SS1 39 International Components for Unicode

Don Brown


"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 18/04/2019
04:05:55 AM:

From: "Mark Murphy" <jmarkmurphy@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"

<midrange-l@xxxxxxxxxxxxxxxxxx>

Date: 18/04/2019 04:05 AM
Subject: Re: [EXT] Re: How can I detect an invalid date string in SQL?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

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

--
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

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud

service.

______________________________________________________________________

______________________________________________________________________
This email has been scanned for computer viruses. Although MSD has taken reasonable precautions to ensure no viruses are present in this email, MSD cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
______________________________________________________________________