CODEWITHSUNDEEP
sqlregexhivehqlimpala
VnS
VnS

Reputation: 27

Regex expression to put "." after sixth position from right in HQL

I have an requirement to put a dot(.) after 6th character from right to left. The total character length is 15 char.

I have below table:

Tab1
---
Col1    Col2
---    -----
1      000000000000098
2      000000000000234
5      000000000024556
6      000000012345677
7      000000013ABC686

Expected Output: Column value should be right adjusted to 6 places after decimal

Col1  Col2
---   ----
1     0.000098
2     0.000234
5     0.024556
6    12.345677
7    13.ABC686

Tried so far in SQL and its working assuming max length of number is 15 char, I am putting "." after 9th Char.

Select
  col
 ,regexp_replace(col,'^([[:digit:]]{9})','\1.') as res
From
tab;

But same doesnt work in HQL.

Result getting in HQL: It always adds 1 instead of taking rest if the char

Col1  Col2
    ---   ----
    1     1.000098
    2     1.000234
    5     1.024556
    6     1.345677

Upvotes: 0

Views: 76

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

If I understand your data requirement correctly, you can simply divide the Col2 values by one million here, after converting the Col2 values from strings to integers.

SELECT Col1, CAST(Col2 AS int) / 1000000.0 AS Col2
FROM tab;

To add the decimal place to the strings, without using actual division, we can try:

SELECT Col1,
       REGEXP_REPLACE(Col2, '(.{6})$', '.$1') AS Col2
FROM tab;

Upvotes: 1

Related Questions