Converting string to date using REGEXP - Getting error 3798

Database
Enthusiast

Converting string to date using REGEXP - Getting error 3798

Hello,

TD gives errors when trying to cast a date that has a single-digit day or month (ie. 1925-7-9).  So, I wrote the code below using REGEXP functions to add a leading zero to the day or month if required.  But it is giving error 3798 (A column or character expression is larger than the max size) even though I am concatenating just a few small strings.  I used similar logic to successfully convert formats like 7/9/1925. (I tried to paste that code, but I am not able to paste code very easily in this forum interface.  It worked once, but now when I Right-click and hit Paste nothing happens).   I saw other solutions for this problem on the forum that use SUBSTR, INDEX, etc, but I find that the REGEXP functions result in more understandable code.  Any help would be appreciated. 

Thanks!

-Greg

-- this results in error 3798 (A column or character expression is larger than the max size.)

SELECT

REGEXP_SUBSTR('1925-7-9', '^[[:digit:]][[:digit:]][[:digit:]][[:digit:]]-') ||

CASE WHEN CHAR_LENGTH(OREPLACE(REGEXP_SUBSTR('1925-7-9', '-[[:digit:]]-'),'-','')) = 1

         THEN '0'||OREPLACE(REGEXP_SUBSTR('1925-7-9', '-[[:digit:]]-'),'-','')

         ELSE OREPLACE(REGEXP_SUBSTR('1925-7-9', '-[[:digit:]][[:digit:]]-'),'-','')

       END ||'-'||

CASE WHEN CHAR_LENGTH(OREPLACE(REGEXP_SUBSTR('1925-7-9', '-[[:digit:]]$'),'-','')) = 1

         THEN '0'||OREPLACE(REGEXP_SUBSTR('1925-7-9', '-[[:digit:]]$'),'-','')

         ELSE OREPLACE(REGEXP_SUBSTR('1925-7-9', '-[[:digit:]][[:digit:]]$'),'-','')

       END;

8 REPLIES
Junior Contributor

Re: Converting string to date using REGEXP - Getting error 3798

Wow, you think this is more understandable? :)

You can use a simple regex to add a leading zero to a single digit:

regexp_replace('1925-7-9', '\b([0-9])\b', '0\1')

Regarding error 3798, both REGEXP_SUBSTR and OREPLACE return VarChar(8000), if you concat you might get over the limit. The workaround is to CAST to a smaller number...

Enthusiast

Re: Converting string to date using REGEXP - Getting error 3798

SELECT regexp_replace('1925-7-9', '\b([0-9])\b-\b([0-9])\b', '0\1-0\2');

Hey Dieter,

Thanks!  Casting worked!   Your other example is interesting, looks like back-referencing, which I am not that familiar with.  But added some logic to handle the day also.  That's awesome!   Guess I havent been on this forum in a while.  Having a hard time pasting code.   It always pastes at the top of the message even if my cursor is elsewhere, and in vintage green-screen.

-Greg

Junior Contributor

Re: Converting string to date using REGEXP - Getting error 3798

Hi Greg,

yep, the "\1" is a back-reference to the first match, i.e. the single digit.

Btw, your regex is overly complicated, it only works for exactly "single digit, minus, single digit", mine adds a leading zero whenever there's a single digit...

Enthusiast

Re: Converting string to date using REGEXP - Getting error 3798

Thanks Dieter.  But when I run your example, I get   1925-07-9,   the replacement only works on the first digit.  Need to also get the second digit so that the output is  1925-07-09.

Junior Contributor

Re: Converting string to date using REGEXP - Getting error 3798

Hi Greg,

I think the defaults for REGEXP_REPLACE changed, it's working fine in 15/15.10, you might try

regexp_replace('1925-7-9 4 1 4', '\b([0-9])\b', '0\1', 1, 0, 'c')

The "0" is the occurance and should mean "all"

Junior Supporter

Re: Converting string to date using REGEXP - Getting error 3798

Hi.

The capturing group will make the expression fail (at least in TD 14, I haven't got any 15x at hand):

REGEXP_REPLACE('1925-3-1','\b([0-9])\b', '0\1', 1, 0, 'c') will give '1925-03-03'.

You can have the good result capturing the hyphen instead with a lookahead:

REGEXP_REPLACE('1925-3-1','(-)(?=[0-9](-|$))','\10',1,0,'c')

It will work with '1925-03-1', '1925-3-01' and '1925-03-01'.

HTH

Cheers.

Carlos.

Enthusiast

Re: Converting string to date using REGEXP - Getting error 3798

Yes, we are on 14.  Now it is working perfectly.  I should have thought to use the all-occurrences arg.   Thanks for your quick response Dieter, as I am under the gun to finish a project and processing/joining on dates is a big part of the remaining work.

-Greg

Enthusiast

Re: Converting string to date using REGEXP - Getting error 3798

Carlos,

Many thanks to you as well !  I am not familiar with lookaheads, but that sounds pretty interesting.  Will check that out when I get out of my current time crunch.

-Greg