0

I have a dynamic sql query that creates a SP. The SSIS package later then loads the data directly from these SP's to Flat Files. In case of Floating point values in tables, we are loading data from SP to flat files. We want the floating point values to be exact in the flat file. We don't want any extra zeroes, incorrect values. We are using following sql convert function in order to keep the values intact but for values such as 8023545654, this convert function trims values. enter image description here Its for SQL Server 2008

SQL Server 2008

Community
  • 1
  • 1
Anshul Dubey
  • 349
  • 4
  • 14
  • So you just need the third version without trailing zeroes? – George Menoutis May 22 '18 at 10:03
  • 2
    please avoid images and post your data as formatted text – B3S May 22 '18 at 10:03
  • What is the table's schema and why are you converting at all? Why cast with a precision of 8 just to round to 3 digits? Besides in math and SQL trailing zeroes don't count. – Panagiotis Kanavos May 22 '18 at 10:03
  • So... what's your question here? When posting a question, you need to actually ask a question (surprise! :) ). Also, don't post code or data as images, they aren't helpful to anyone. – Thom A May 22 '18 at 10:03
  • Also, on a different note, avoid the use of `float`, it's not an accurate datatype. You are far better off using `decimal`. – Thom A May 22 '18 at 10:04
  • If you want precise numbers, use the `decimal/numeric` type, not unsuitable types like `nvarchar`. Why do you assume that the client will use `.` as the decimal separator? – Panagiotis Kanavos May 22 '18 at 10:05
  • So your base data is float? If that's the case then why are you converting it to float at all? If you simply converted it straight to `NVARCHAR(50)` would that not work? `SELECT id, CONVERT(NVARCHAR(50), new) FROM dbo.tempforAnshul;` – Richard Hansell May 22 '18 at 10:06
  • Yes, I want the third version without trailing zeroes, and SSIS picks from OLEDB source correctly but when it dumps data to flat file it adds zeroes automatically somehow. – Anshul Dubey May 22 '18 at 10:07
  • 1
    You say without the trailing zeroes, however, you have `45.6700000` ,`45.6750000` and `801245566.000000` If you put them to the highest used precision you have `45.670`, `45.675` and `801245566.000`. is that what you're after? You can't have `45.67`, `45.675` and `801245566` as those are different data types; and you can't mix datatypes in a column (well, technically they're all `decimal`, but with different scales and precisions). – Thom A May 22 '18 at 10:11
  • take a look at this: https://stackoverflow.com/a/18520357/6557198 note: possible duplicate of linked question (above)? – B3S May 22 '18 at 10:12
  • Possible duplicate of [Remove trailing zeros from decimal in SQL Server](https://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server) – B3S May 22 '18 at 10:14
  • As of now, I have values such as 45.67, 45.675 and 801245566 in a single column of Float type. But in order to keep the values intact in the flat file i was using the below query convert(NVARCHAR(50),CONVERT(float,new)) which is working fine but values such as 801245566 are getting truncated. When I use convert(NVARCHAR(50),(CONVERT(decimal(30,8),new))) it adds extra values due to the precision which I don't want. – Anshul Dubey May 22 '18 at 10:21
  • If the issue is simply the formatting, then worry about that in your presentation layer; not SQL Server. – Thom A May 22 '18 at 10:43
  • Values such as 801245566 aren't suitable for float data type, they can be stored in bigint, the values will be stored succesfully but will get truncated once we insert them in flat file. – Anshul Dubey Sep 04 '18 at 09:59

2 Answers2

0

Try this?

DECLARE @tempforAnshul TABLE (id INT, new FLOAT);
INSERT INTO @tempforAnshul SELECT 1, 45.67;
INSERT INTO @tempforAnshul SELECT 2, 45.675;
INSERT INTO @tempforAnshul SELECT 3, 123.45;
INSERT INTO @tempforAnshul SELECT 4, 345.34;
INSERT INTO @tempforAnshul SELECT 5, 8023545654;
SELECT id, FORMAT(new, 'G') FROM @tempforAnshul;

Gives you the following results:

id  (No column name)
1   45.67
2   45.675
3   123.45
4   345.34
5   8023545654

It's probably also worth noting that FORMAT returns either NULL or an NVARCHAR, which seems to be what you want? However, you can't control the length of the NVARCHAR, so you might need to be a little careful here?

...and then I re-read your question and saw you were using SQL Server 2008. Well that's a pain as FORMAT didn't come in until 2012, but this hack would work:

WITH x AS (
    SELECT id, REVERSE(CONVERT(NVARCHAR(50), CONVERT(DECIMAL(30,8), new))) AS val FROM @tempforAnshul),
y AS (
    SELECT id, REVERSE(SUBSTRING(val, PATINDEX('%[^0]%', val), 100)) AS val FROM x)
SELECT
    id,
    CASE 
        WHEN RIGHT(val, 1) = '.' THEN LEFT(val, LEN(val)-1)
        ELSE val
    END AS val
FROM
    y;
Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
0

The best possible way to convert a floating point value that the values are not truncated could be 

Select CONVERT(DECIMAL(18,7),columnname),128)[columnname] From TableName

It helped me to conserve most of the floating point values in the way they were while inserting them in the text file.

Suggestions are welcome

Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86
Anshul Dubey
  • 349
  • 4
  • 14