All Forums Database
gpolanch 46 posts Joined 12/11
02 Mar 2016
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;
 
 
 

dnoeth 4628 posts Joined 11/04
02 Mar 2016

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

Dieter

gpolanch 46 posts Joined 12/11
02 Mar 2016
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
 
 
 

dnoeth 4628 posts Joined 11/04
02 Mar 2016

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

Dieter

gpolanch 46 posts Joined 12/11
02 Mar 2016

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.

dnoeth 4628 posts Joined 11/04
02 Mar 2016

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"
 

Dieter

CarlosAL 512 posts Joined 04/08
02 Mar 2016

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.
 
 
 

gpolanch 46 posts Joined 12/11
02 Mar 2016

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
 

gpolanch 46 posts Joined 12/11
02 Mar 2016

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
 

You must sign in to leave a comment.