Example of the data in csv

Column_header

000000025000{

000000007185E

The doucmention I have

*The right most position of the numeric field will have a sign OVER it designating positive or negative.

Example of Data

I dont understand how write the logic to support the the symbol,number,letter to get the correct value.

CodePudding user response：

I'd create a table (or view) with the static mapping of character-value, meaning:

about the data rows themselves, it seems to me there is always a symbol at the end, therefore you can split the data into two columns, value, and symbol...

I have no idea about how the data are inserted but it seems logically easy

SELECT
   _YourValue_
  ,LEFT(_YourValue_, LENGTH(_YourValue_)-1) as Value
  ,RIGHT(_YourValue_, 1) as Symbol
FROM _Whatever_

you can also cast to whatever datatype is correct for those data. Finally you can join the tables and show/calculate whatever is needed

CodePudding user response：

select value , if(value LIKE '%{%' or value LIKE '%J%' or value LIKE '%E%' or value LIKE '%C%',concat(SUBSTRING(value,1,char_length(value)-1),' '),concat(SUBSTRING(value,1,char_length(value)-1),'-')) as new_value from yourtablename

Output

Add all other character on first parameter of if clause for positive designation.