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