September 22, 2014 at 2:41 pm
I have to store the result of a calculation in a column of type CHAR(7) (and am unable to change the column type).
The calculation can have results ranging in size from 0.1234567 to 99999999.
In the first case, I would need to store the value of 0.12345 in the column. In the later case, an error should be thrown.
So I need to store all of the significant digits from the left of the decimal (if there are < 7) and as many of the digits to the right as will fit into a CHAR(7), with the remaining precision being truncated.
Before I start writing what I suspect will be a very ugly UDF, does anyone know of an easier method of accomplishing this task?
September 22, 2014 at 2:58 pm
Am I missing something here? Wouldn't a simple LEFT() do the job?
WITH SampleData AS(
SELECT RAND(object_id) * 1000 N
FROM sys.all_columns
)
SELECT N, LEFT( N, 7)
FROM SampleData
September 22, 2014 at 3:18 pm
Luis Cazares (9/22/2014)
Am I missing something here? Wouldn't a simple LEFT() do the job?
The only problem with using LEFT() is if the value to the left of the decimal is exactly 6 digits wide, then a "dangling decimal point" will be left, but I can code around that.
For some reason I had gotten target fixation on using CONVERT() alone. Thanks.
September 22, 2014 at 10:15 pm
More for fun, here is an alternative "solution", set to return six digits and the decimal separator.
😎
USE tempdb;
GO
SET NOCOUNT ON
/* Generate a set of numbers from 1 to
the value of @SAMPLE_SIZE
*/
DECLARE @SAMPLE_SIZE INT = 10;
DECLARE @TEST_SET TABLE (RID INT NOT NULL, FL_VAL FLOAT NOT NULL);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) AS
N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
/* Generate floating point number test set */
INSERT INTO @TEST_SET(RID,FL_VAL)
SELECT
NM.N
,ABS(CHECKSUM(NEWID())) / POWER(10.0,ABS(CHECKSUM(NEWID())) % 11.0)
FROM NUMS NM
SELECT
TS.RID AS RID
,TS.FL_VAL AS FLOAT_VALUE
/* If there is a remainder after the division then
the number is too great
*/
,1 -SIGN(FLOOR(TS.FL_VAL / 100000)) AS IS_VALID
/* Return as string
*/
,STR(TS.FL_VAL,7,7) AS CHOPPED_STRING_7
FROM @TEST_SET TS
/* Uncomment the next line to filter only valid numbers */
--WHERE (1 -SIGN(FLOOR(TS.FL_VAL / 100000))) = 1
Sample results
RID FLOAT_VALUE IS_VALID CHOPPED_STRING_7
----------- ---------------------- ---------------------- ----------------
1 769388.178 0 769388
2 2.71267499 1 2.71267
3 176.762866 1 176.763
4 1949056 0 1949056
5 1300132.345 0 1300132
6 1.65171232 1 1.65171
7 0.913729839 1 0.91373
8 1338.032751 1 1338.03
9 70.206716 1 70.2067
10 1974091608 0 *******
September 23, 2014 at 8:57 am
Eirikur Eiriksson (9/22/2014)
More for fun, here is an alternative "solution", set to return six digits and the decimal separator.😎
I like this solution because it handles the "dangling decimal point" issue, and you've even provided a validity test as a bonus!
Unfortunately the STR() function rounds the value, and I need to truncate it instead (though the random nature of the values make using the ROUND() function for truncation problematic).
September 23, 2014 at 3:40 pm
Here's what I'd do. Cast whatever your original float input is as a very large decimal (say (38,10)) then cast that to a string. It will retain all the points of precision as opposed to when you convert a floating point expression to a string.
Next, determine where the decimal point lies for each string. If It's in the 7th position, chop it off with a case statement and only return the left 6 (thus solving the "floating decimal point issue"). Any other decimal place, return left 7.
Finally, omit any record where the decimal point is >= 8 (so your 99999999 example falls out)
;with sampleData as
(
select top 10000 cast(cast((abs(checksum(newid())) % 100000) * rand() as decimal(38,10)) as varchar(50)) as num
from sys.all_columns
union all select cast(0.1234567 as decimal(38,10))
union all select cast(999999999 as decimal(38,10))
), tDec as
(
select decPt = charindex('.', num), num
from sampleData
)
select left(num, 7)
from tDec
where decPt < 8
order by num desc
October 4, 2014 at 3:49 pm
samp.silvercreek (9/23/2014)
Eirikur Eiriksson (9/22/2014)
More for fun, here is an alternative "solution", set to return six digits and the decimal separator.😎
I like this solution because it handles the "dangling decimal point" issue, and you've even provided a validity test as a bonus!
Unfortunately the STR() function rounds the value, and I need to truncate it instead (though the random nature of the values make using the ROUND() function for truncation problematic).
Better late than never, here is an alternative;-)
😎
USE tempdb;
GO
SET NOCOUNT ON
/* Generate a set of numbers from 1 to
the value of @SAMPLE_SIZE
*/
DECLARE @SAMPLE_SIZE INT = 10;
DECLARE @TEST_SET TABLE (RID INT NOT NULL, FL_VAL FLOAT NOT NULL);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) AS
N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
/* Generate floating point number test set */
INSERT INTO @TEST_SET(RID,FL_VAL)
SELECT
NM.N
,ABS(CHECKSUM(NEWID())) / POWER(10.0,ABS(CHECKSUM(NEWID())) % 11.0)
FROM NUMS NM
SELECT
TS.RID AS RID
,TS.FL_VAL AS FLOAT_VALUE
/* If there is a remainder after the division then
the number is too great
*/
,1 -SIGN(FLOOR(TS.FL_VAL / 100000)) AS IS_VALID
/* Return as string
*/
--,STR(TS.FL_VAL,7,7) AS CHOPPED_STRING_7
,SUBSTRING(CONVERT(VARCHAR(40),CONVERT(DECIMAL(38,5),TS.FL_VAL,1),1),1,7) AS CHOPPED_STRING_7
FROM @TEST_SET TS
/* Uncomment the next line to filter only valid numbers */
--WHERE (1 -SIGN(FLOOR(TS.FL_VAL / 100000))) = 1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply