Home > database >  How to add delimiter to String after every n character using hive functions?
How to add delimiter to String after every n character using hive functions?

Time:03-16

I have the hive table column value as below.

"112312452343"

I want to add a delimiter such as ":" (i.e., a colon) after every 2 characters.

I would like the output to be:

11:23:12:45:23:43

Is there any hive string manipulation function support available to achieve the above output?

CodePudding user response:

For fixed length this will work fine:

select regexp_replace(str, "(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})","$1:$2:$3:$4:$5:$6")
from
(select "112312452343" as str)s

Result:

11:23:12:45:23:43

Another solution which will work for dynamic length string. Split string by the empty string that has the last match (\\G) followed by two digits (\\d{2}) before it ((?<= )), concatenate array and remove delimiter at the end (:$):

select regexp_replace(concat_ws(':',split(str,'(?<=\\G\\d{2})')),':$','')
from
(select "112312452343" as str)s

Result:

11:23:12:45:23:43

If it can contain not only digits, use dot (.) instead of \\d:

regexp_replace(concat_ws(':',split(str,'(?<=\\G..)')),':$','')

CodePudding user response:

This is actually quite simple if you're familiar with regex & lookahead.
Replace every 2 characters that are followed by another character, with themselves ':'

select regexp_replace('112312452343','..(?=.)','$0:')

 ------------------- 
|        _c0        |
 ------------------- 
| 11:23:12:45:23:43 |
 ------------------- 
  • Related