CODEWITHSUNDEEP
sqlstringhivehiveqldelimiter
Vadivel A
Vadivel A

Reputation: 51

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

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?

Upvotes: 1

Views: 868

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

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

Upvotes: 2

leftjoin
leftjoin

Reputation: 38335

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..)')),':$','')

Upvotes: 2

Related Questions