· 8 years ago · May 20, 2018, 08:28 AM
1CREATE FUNCTION dbo.ConvertAmountVerified
2(
3 @amt VARCHAR(50)
4)
5RETURNS NUMERIC (18,3)
6AS
7BEGIN
8 -- Declare the return variable here
9 DECLARE @Amount NUMERIC(18, 3);
10 DECLARE @TempAmount VARCHAR (50);
11 DECLARE @Num VARCHAR(1);
12 DECLARE @LastChar VARCHAR(1);
13 DECLARE @Negative BIT ;
14 -- Get Last Character
15 SELECT @LastChar = RIGHT(RTRIM(@amt), 1) ;
16
17 SELECT @Num =
18 CASE @LastChar collate latin1_general_cs_as
19 WHEN '{' THEN '0'
20 WHEN 'A' THEN '1'
21 WHEN 'B' THEN '2'
22 WHEN 'C' THEN '3'
23 WHEN 'D' THEN '4'
24 WHEN 'E' THEN '5'
25 WHEN 'F' THEN '6'
26 WHEN 'G' THEN '7'
27 WHEN 'H' THEN '8'
28 WHEN 'I' THEN '9'
29 WHEN '}' THEN '0'
30 WHEN 'J' THEN '1'
31 WHEN 'K' THEN '2'
32 WHEN 'L' THEN '3'
33 WHEN 'M' THEN '4'
34 WHEN 'N' THEN '5'
35 WHEN 'O' THEN '6'
36 WHEN 'P' THEN '7'
37 WHEN 'Q' THEN '8'
38 WHEN 'R' THEN '9'
39 ---ASCII
40 WHEN 'p' Then '0'
41 WHEN 'q' Then '1'
42 WHEN 'r' Then '2'
43 WHEN 's' Then '3'
44 WHEN 't' Then '4'
45 WHEN 'u' Then '5'
46 WHEN 'v' Then '6'
47 WHEN 'w' Then '7'
48 WHEN 'x' Then '8'
49 WHEN 'y' Then '9'
50 ELSE ''
51 END;
52
53 SELECT @Negative =
54 CASE @LastChar collate latin1_general_cs_as
55 WHEN '{' THEN 0
56 WHEN 'A' THEN 0
57 WHEN 'B' THEN 0
58 WHEN 'C' THEN 0
59 WHEN 'D' THEN 0
60 WHEN 'E' THEN 0
61 WHEN 'F' THEN 0
62 WHEN 'G' THEN 0
63 WHEN 'H' THEN 0
64 WHEN 'I' THEN 0
65 WHEN '}' THEN 1
66 WHEN 'J' THEN 1
67 WHEN 'K' THEN 1
68 WHEN 'L' THEN 1
69 WHEN 'M' THEN 1
70 WHEN 'N' THEN 1
71 WHEN 'O' THEN 1
72 WHEN 'P' THEN 1
73 WHEN 'Q' THEN 1
74 WHEN 'R' THEN 1
75
76 ---ASCII
77 WHEN 'p' Then '1'
78 WHEN 'q' Then '1'
79 WHEN 'r' Then '1'
80 WHEN 's' Then '1'
81 WHEN 't' Then '1'
82 WHEN 'u' Then '1'
83 WHEN 'v' Then '1'
84 WHEN 'w' Then '1'
85 WHEN 'x' Then '1'
86 WHEN 'y' Then '1'
87 ELSE 0
88 END;
89 -- Add the T-SQL statements to compute the return value here
90 if (@Num ='')
91 BEGIN
92 SELECT @TempAmount=@amt;
93 END;
94 ELSE
95 BEGIN
96 SELECT @TempAmount = SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + @Num;
97 END;
98
99 SELECT @Amount =CASE @Negative
100 WHEN 0 THEN (CAST(@TempAmount AS NUMERIC) / 100)
101 WHEN 1 THEN (CAST (@TempAmount AS NUMERIC) /100) * -1
102 END ;
103 -- Return the result of the function
104 RETURN @Amount;
105END;
106GO
107
108DROP FUNCTION IF EXISTS dbo.ConvertAmountVerified_TVF
109GO
110CREATE FUNCTION dbo.ConvertAmountVerified_TVF
111(
112 @amt VARCHAR(50)
113)
114RETURNS TABLE
115WITH SCHEMABINDING
116AS
117RETURN
118(
119 WITH cteLastChar
120 AS(
121 SELECT LastChar = RIGHT(RTRIM(@amt), 1)
122 )
123 SELECT
124 AmountVerified = CAST(RET.Y AS NUMERIC(18,3))
125 FROM (SELECT 1 t) t
126 OUTER APPLY (
127 SELECT Num =
128 CAST(
129 CASE
130 WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
131 THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0)-1
132 WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0) >0
133 THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0)-1
134 WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0) >0
135 THEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0)-1
136 ELSE NULL
137 END
138 AS VARCHAR(2))
139 FROM
140 cteLastChar L
141 ) NUM
142 OUTER APPLY (
143 SELECT Negative =
144 CASE
145 WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
146 THEN 0
147 WHEN CHARINDEX(L.LastChar COLLATE Latin1_General_CS_AS, 'JKLMNOPQRpqrstuvwxy', 0) >0
148 THEN 1
149 ELSE 0
150 END
151 FROM cteLastChar L
152 ) NEG
153 OUTER APPLY(
154 SELECT Amt= CASE
155 WHEN NUM.Num IS NULL
156 THEN @amt
157 ELSE
158 SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + Num.Num
159 END
160 ) TP
161 OUTER APPLY(
162 SELECT Y = CASE
163 WHEN NEG.Negative = 0
164 THEN (CAST(TP.Amt AS NUMERIC) / 100)
165 WHEN NEG.Negative = 1
166 THEN (CAST (TP.Amt AS NUMERIC) /100) * -1
167 END
168 ) RET
169) ;
170
171GO
172
173SELECT dbo.ConvertAmountVerified('00064170') -- 641.700
174SELECT * FROM dbo.ConvertAmountVerified_TVF('00064170') -- 641.700
175
176SELECT dbo.ConvertAmountVerified('00057600A') -- 5760.010
177SELECT * FROM dbo.ConvertAmountVerified_TVF('00057600A') -- 5760.010
178
179SELECT dbo.ConvertAmountVerified('00059224y') -- -5922.490
180SELECT * FROM dbo.ConvertAmountVerified_TVF('00059224y') -- -5922.490
181
182-- Get Last Character
183SELECT @LastChar = RIGHT(RTRIM(@amt), 1) collate latin1_general_cs_as;
184
185DECLARE @CharPos int=NULLIF(CHARINDEX(@LastChar,'{ABCDEFGHI}JKLMNOPQRpqrstuvwxy'),0)-1
186SET @Num = ISNULL(@CharPos%10,'');
187SET @Negative = IIF(@CharPos>9,1,0);
188
189SELECT @Num =
190 CASE @LastChar collate latin1_general_cs_as
191 WHEN '{' THEN '0'
192...
193
194SELECT @Negative =
195 CASE @LastChar collate latin1_general_cs_as
196 WHEN '{' THEN 0
197...
198
199-- auxiliary table
200CREATE TABLE LastCharLink(
201 LastChar varchar(1) collate latin1_general_cs_as NOT NULL,
202 Num varchar(1) NOT NULL,
203 Prefix varchar(1) NOT NULL,
204CONSTRAINT PK_LastCharLink PRIMARY KEY(LastChar)
205)
206
207INSERT LastCharLink(LastChar,Num,Prefix)VALUES
208('{','0',''),
209('A','1',''),
210('B','2',''),
211('C','3',''),
212('D','4',''),
213('E','5',''),
214('F','6',''),
215('G','7',''),
216('H','8',''),
217('I','9',''),
218('}','0','-'),
219('J','1','-'),
220('K','2','-'),
221('L','3','-'),
222('M','4','-'),
223('N','5','-'),
224('O','6','-'),
225('P','7','-'),
226('Q','8','-'),
227('R','9','-'),
228('p','0','-'),
229('q','1','-'),
230('r','2','-'),
231('s','3','-'),
232('t','4','-'),
233('u','5','-'),
234('v','6','-'),
235('w','7','-'),
236('x','8','-'),
237('y','9','-')
238
239CREATE TABLE #TestAmounts(Amt varchar(10))
240INSERT #TestAmounts(Amt)VALUES('00064170'),('00057600A'),('00066294R'),('00059224}'),('00012345p')
241
242SELECT
243 *,
244 CAST( -- step 5 - final cast
245 CAST( -- step 3 - convert to number
246 CONCAT( -- step 2 - add a sign and an additional number
247 l.Prefix,
248 LEFT(RTRIM(a.Amt),LEN(RTRIM(a.Amt))-IIF(l.LastChar IS NULL,0,1)), -- step 1 - remove last char
249 l.Num
250 )
251 AS numeric(18,3)
252 )/100 -- step 4 - divide
253 AS numeric(18,3)
254 ) ResultAmt
255FROM #TestAmounts a
256LEFT JOIN LastCharLink l ON RIGHT(RTRIM(a.Amt),1) collate latin1_general_cs_as=l.LastChar
257
258DROP TABLE #TestAmounts
259
260DECLARE @LastCharLink TABLE(
261 LastChar varchar(1) collate latin1_general_cs_as NOT NULL,
262 Num varchar(1) NOT NULL,
263 Prefix varchar(1) NOT NULL,
264PRIMARY KEY(LastChar)
265)
266
267INSERT LastCharLink(LastChar,Num,Prefix)VALUES
268('{','0',''),
269('A','1',''),
270('B','2',''),
271('C','3',''),
272('D','4',''),
273('E','5',''),
274...
275
276FROM #TestAmounts a
277LEFT JOIN #LastCharLink l ON ...
278
279FROM #TestAmounts a
280LEFT JOIN @LastCharLink l ON ...
281
282CREATE FUNCTION NewConvertAmountVerified(
283 @Amt varchar(50),
284 @LastChar varchar(1),
285 @Num varchar(1),
286 @Prefix varchar(1)
287)
288RETURNS numeric(18,3)
289AS
290BEGIN
291 RETURN CAST( -- step 3 - convert to number
292 CONCAT( -- step 2 - add a sign and an additional number
293 @Prefix,
294 LEFT(@Amt,LEN(@Amt)-IIF(@LastChar IS NULL,0,1)), -- step 1 - remove last char
295 @Num
296 )
297 AS numeric(18,3)
298 )/100 -- step 4 - divide
299END
300GO
301
302CREATE TABLE #TestAmounts(Amt varchar(10))
303INSERT #TestAmounts(Amt)VALUES('00064170'),('00057600A'),('00066294R'),('00059224}'),('00012345p')
304
305SELECT
306 *,
307 -- you need to use `RTRIM` here
308 dbo.NewConvertAmountVerified(RTRIM(a.Amt),l.LastChar,l.Num,l.Prefix) ResultAmt
309FROM #TestAmounts a
310LEFT JOIN LastCharLink l ON RIGHT(RTRIM(a.Amt),1) collate latin1_general_cs_as=l.LastChar
311
312DROP TABLE #TestAmounts