December 28, 2017 at 9:19 pm
I need to take numerical data from a CSV file and format it as follows:
Value in File - Converted Value
3 0030
25.75 0258
12 0120
125.8 1258
The converted value must contain 4 numbers, rounded to the nearest 10th, numbers only, no decimal points. I am using SQL Tools on SQL Server 2012 to import the data into a table in the SQL Server, then using t-sql in a stored procedure to convert the data and export the file as a CSV for use in another system.
I am having a very difficult time getting the leading or ending zeros as needed, along with rounding to the nearest 10th.
Thank you in advance for any help you can provide.
December 29, 2017 at 1:18 am
Not sure you are going to achieve that without changing the data type to a character string, having said that something along the lines of the following may help:
DECLARE @Num decimal (6,2) = 3.55
SELECT LEFT('0000', 4 - LEN(CAST((@NUM * 10) as INT))) + CAST ((CAST(ROUND((@NUM * 10), 0) as INT)) as varchar(4)) ConvVal
...
December 29, 2017 at 2:21 am
Slightly different approach. This also won't fall over if a number is greater than 999:
WITH VTE AS (
SELECT *
FROM (VALUES(3),(25.75),(12),(125.8),(1000),(1857.5)) V(I))
SELECT I,
RIGHT('0000' + CONVERT(varchar(8),CONVERT(int,ROUND(I*10,0))),4) AS LeadingZeros
FROM VTE;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 29, 2017 at 6:40 am
A shorter version:WITH VTE AS (
SELECT *
FROM (VALUES(3),(25.75),(12),(125.8),(1000),(1857.5)) V(I))
SELECT I,
RIGHT( 10000 + CAST(ROUND(I*10, 0) AS int), 4) AS LeadingZeros
FROM VTE;
December 29, 2017 at 8:47 am
This is an even simpler option:
WITH VTE AS
(
SELECT
*
FROM
(
VALUES (3),
(25.75),
(12),
(125.8),
(1000),
(1857.5)
) V (I)
)
SELECT VTE.I, FORMAT(I*10, '0000') AS LeadingZeros FROM VTE;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2017 at 8:56 am
Jack Corbett - Friday, December 29, 2017 8:47 AMThis is an even simpler option:
WITH VTE AS
(
SELECT
*
FROM
(
VALUES (3),
(25.75),
(12),
(125.8),
(1000),
(1857.5)
) V (I)
)
SELECT VTE.I, FORMAT(I*10, '0000') AS LeadingZeros FROM VTE;
I usually avoid FORMAT due to its fame of being slow.
December 29, 2017 at 4:00 pm
HappyGeek - Friday, December 29, 2017 1:18 AMNot sure you are going to achieve that without changing the data type to a character string, having said that something along the lines of the following may help:
DECLARE @Num decimal (6,2) = 3.55
SELECT LEFT('0000', 4 - LEN(CAST((@NUM * 10) as INT))) + CAST ((CAST(ROUND((@NUM * 10), 0) as INT)) as varchar(4)) ConvVal
Thank you, HappyGeek! this worked perfectly. Have a Happy New Year.
December 29, 2017 at 4:30 pm
Luis Cazares - Friday, December 29, 2017 8:56 AMJack Corbett - Friday, December 29, 2017 8:47 AMThis is an even simpler option:
WITH VTE AS
(
SELECT
*
FROM
(
VALUES (3),
(25.75),
(12),
(125.8),
(1000),
(1857.5)
) V (I)
)
SELECT VTE.I, FORMAT(I*10, '0000') AS LeadingZeros FROM VTE;I usually avoid FORMAT due to its fame of being slow.
I was just comparing the two for that very reason....format version is much slower on execution times as I thought it would be. Quite noticeable with a cold cache even with a small set of data. Keep thinking maybe there is some scenario where it is more useful, isn't so slow but haven't found one yet.
Sue
January 3, 2018 at 1:49 am
Sue_H - Friday, December 29, 2017 4:30 PMLuis Cazares - Friday, December 29, 2017 8:56 AMJack Corbett - Friday, December 29, 2017 8:47 AMThis is an even simpler option:
WITH VTE AS
(
SELECT
*
FROM
(
VALUES (3),
(25.75),
(12),
(125.8),
(1000),
(1857.5)
) V (I)
)
SELECT VTE.I, FORMAT(I*10, '0000') AS LeadingZeros FROM VTE;I usually avoid FORMAT due to its fame of being slow.
I was just comparing the two for that very reason....format version is much slower on execution times as I thought it would be. Quite noticeable with a cold cache even with a small set of data. Keep thinking maybe there is some scenario where it is more useful, isn't so slow but haven't found one yet.
Sue
Agreed, I remember about 4 years ago when I got access to FORMAT on 2012, and I was really excited. Started using it, and noticed a significant performance decrease. Couldn't see at all as to why; then someone (here) posted about FORMAT and it's awful performance; i changed the FORMAT back to CONVERT and they query runs beautifully again.
A real shame, as FORMAT is so much easier on the eyes; something that starts with REPLACE(LEFT(CONVERT(varchar(15), and ends with something like ,121),7),'-','') just isn't as easily understood by some.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply