Per a vendor, for a negative number, the last character must be converted to a capital letter.
J=-1
K=-2
L=-3
M=-4
N=-5
O=-6
P=-7
Q=-8
R=-9
)=-0
For example, -123.45 should be sent over as 1234N.
I couldn't find anything in the to_char that would do this.
My next step is to use a function, pass the number to it, and using substring to determine last character.
Any idea is appreciated.
Thanks!
Just a gentle reminder to everyone that asks questions.
You have all read:
http://www.flickr.com/photos/tkyte/4033155188/sizes/o/ when submitting a question. You check a checkbox saying you read it. Many (most I would say) do not. #3 is the most important point on that URL. It is really hard to write a sql query for you without a create table, some inserts into it. I made up data for this one - but it really should have come with a table and some sample data....
ops$tkyte%ORA11GR2> select x,
2 case
3 when x >= 0
4 then to_char(x)
5 else replace(substr( to_char(abs(x)),1,length(to_char(abs(x)))-1),'.','') ||
6 decode (to_number(substr(to_char(abs(x)), length(to_char(abs(x))))),
7 1 , 'J',
8 2 , 'K',
9 3 , 'L',
10 4 , 'M',
11 5 , 'N',
12 6 , 'O',
13 7 , 'P',
14 8 , 'Q',
15 9 , 'R',
16 0 , ')' )
17 end new_x
18 from t;
X NEW_X
---------- -----------------------------------------
26.2975 26.2975
-93.7266 93726O
4.2339 4.2339
-20.6391 20639J
67.4443 67.4443
-45.4447 45444P
76.4008 76.4008
-30.3166 30316O
28.0645 28.0645
-97.666 9766O
10 rows selected.
that is one approach you can take.