Convert string to big decimal

[NNayal1600240775]

Hi ,

I have data like 00016546M,00000203Q.

Where the last digit alphabet represents:

M: -1

N: -2

O: -3

P : -4

Q: -5

and so on.

My o/p should be convert the last digit to the equivalent number but the minus "-" sign should be at the beginning. and then divide by 100.

For eg if i/p is 00000200P THEN o/p should be -20.07.

How can i achieve this in tmap?

[Anonymous]

Hi

Use org.apache.commons.lang3.StringUtils.stripStart("00016546M", "0") will strip the start character "0", and use tReplaceList will replace the letter with equivalent number.

Regard

Shong

[NNayal1600240775]

Hi Shong through query I am able to achieve the result.

Below is the query for 1 sample:

SELECT CASE WHEN '000015O' LIKE '%O' THEN CONCAT('-',SUBSTRING('000015O',1,(regexp_instr('000015O','O'))-1) ,'3')/100  END;

Now I need to convert this query in tmap. Could you please help

[Anonymous]

On tMap, use expression:

"-"+org.apache.commons.lang3.StringUtils.stripStart(row1.value, "0") 

Before tMap, you need to use tReplaceList to replace the letter with equivalent number.

[vikramk · Accepted solution]

Hi NNayal1600240775,

Please follow below approach, make necessary changes as per your requirement

in tmap:

converted string to decimal

in next tmap

Output:

[NNayal1600240775]

Vikram the approach you discussed looks good to me.

But this approach has 3 steps in between , to avoide that I loaded everything as string in a temporary table in snowflake then wrote sql query :

CASE 

WHEN COLUMN_NAME LIKE '%J' THEN CAST(CONCAT('-',SUBSTRING(COLUMN_NAME,1,(regexp_instr(COLUMN_NAME,'J'))-1),'1') AS INTEGER)/100

WHEN COLUMN_NAME LIKE '%K' THEN CAST(CONCAT('-',SUBSTRING(COLUMN_NAME,1,(regexp_instr(COLUMN_NAME,'K'))-1),'2') AS INTEGER)/100

Thanks for sending me this approach in tmap. I will try this out.

Thanks for the help. Really appreciate it.