I have an integrated service that imports raw data from a text file into a SQL Server table. I want to build a temp table using a SELECT query that will give me a column with transaction amounts, by parsing the raw data starting at position 20 for a length of 13 characters. Once parsed, the data looks like the samples below.
I need to remove all leading 0's and then sum the amounts, and make the 2 right most characters decimals. How can I do this? The raw data is originally imported to a varchar(250) column.
Sample of raw data:
The sum should be: $52,065.25
Thanks in advance,