November 2, 2016 at 2:57 pm
How can I use SQL to remove a decimal from a field value.
Current value = 1.66770
Correct value = 0166770
November 2, 2016 at 3:42 pm
There are lots of ways.
You could convert it to a varchar(10), then find and replace the '.' with an empty string, then add a string with zero to the front.
Have a go.
November 2, 2016 at 3:47 pm
maria.lindquist (11/2/2016)
How can I use SQL to remove a decimal from a field value.Current value = 1.66770
Correct value = 0166770
what datatype is "current value"?
what would you expect for Current Values of
111111.654321
0.1234567
-100.987
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 2, 2016 at 3:48 pm
The current value is of varchar(7) type.
November 2, 2016 at 6:46 pm
Given what I know thus far, I would perform a split of the string on the period and keep everything in the first item. Even there's more than one decimal in the string, only the first one gets kept. See the link in my signature for Jeff's excellent article on splitting strings.
There are, of course, quite a few other approaches. An answer to J Livingston's very valid question could change the approach.
November 3, 2016 at 4:11 am
maria.lindquist (11/2/2016)
The current value is of varchar(7) type.
ok, without you answering the rest of my question for expected results on various scenarios...here is shot in the dark :
CREATE TABLE #test(CurrentValue VARCHAR(7));
INSERT INTO #test(CurrentValue) VALUES ('1.66770');
INSERT INTO #test(CurrentValue) VALUES ('-0.0001');
INSERT INTO #test(CurrentValue) VALUES ('-100000');
INSERT INTO #test(CurrentValue) VALUES ('-10.12');
INSERT INTO #test(CurrentValue) VALUES ('0.123');
INSERT INTO #test(CurrentValue) VALUES ('502.321');
INSERT INTO #test(CurrentValue) VALUES ('100000');
SELECT currentvalue,
newvalue = CASE
WHEN LEFT(currentvalue, 1) = '-'
THEN '-'+RIGHT('00000'+REPLACE(REPLACE(currentvalue, '.', ''), '-', ''), 6)
ELSE RIGHT('000000'+REPLACE(currentvalue, '.', ''), 7)
END
FROM #test;
DROP TABLE #test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 3, 2016 at 8:12 am
what datatype is "current value"?
what would you expect for Current Values of
111111.654321
0.1234567
-100.987
The type of field is varchar(7)
For a number like 1.22345 I would expect 0122345
For a number like 0.34567 I would expect 0034567
For a number like 0.34500 I would expect 0034500
November 3, 2016 at 8:44 am
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(NUMBERS) AS
( SELECT CONVERT(VARCHAR(7),X.NUMBERS,0)
FROM ( VALUES
('1.22345')
,('0.34567')
,('0.34500')
) X(NUMBERS)
)
SELECT
SD.NUMBERS
,STUFF('0000000'
,8 - LEN(REPLACE(SD.NUMBERS,CHAR(46),''))
,LEN(REPLACE(SD.NUMBERS,CHAR(46),''))
,REPLACE(SD.NUMBERS,CHAR(46),'')) AS STUFFED_NUMBER
FROM SAMPLE_DATA SD;
Output
NUMBERS STUFFED_NUMBER
------- ---------------
1.22345 0122345
0.34567 0034567
0.34500 0034500
November 3, 2016 at 11:42 am
SELECT
SD.NUMBERS
,RIGHT('0000000' + REPLACE(NUMBERS, '.', ''), 7)
FROM SAMPLE_DATA SD;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 3, 2016 at 11:59 am
ScottPletcher (11/3/2016)
SELECT
SD.NUMBERS
,RIGHT('0000000' + REPLACE(NUMBERS, '.', ''), 7)
FROM SAMPLE_DATA SD;
which I believe is what I posted earlier
CASE <snip> ELSE RIGHT('000000'+REPLACE(currentvalue, '.', ''), 7)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 3, 2016 at 12:18 pm
J Livingston SQL (11/3/2016)
ScottPletcher (11/3/2016)
SELECT
SD.NUMBERS
,RIGHT('0000000' + REPLACE(NUMBERS, '.', ''), 7)
FROM SAMPLE_DATA SD;
which I believe is what I posted earlier
CASE <snip> ELSE RIGHT('000000'+REPLACE(currentvalue, '.', ''), 7)
I missed that part of your post. Although you did use only 6 zeros, which would leave a digit short if the initial column value was blank :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 3, 2016 at 12:37 pm
ScottPletcher (11/3/2016)
J Livingston SQL (11/3/2016)
ScottPletcher (11/3/2016)
I missed that part of your post. Although you did use only 6 zeros, which would leave a digit short if the initial column value was blank :-).
tired eyes 😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 3, 2016 at 4:13 pm
J Livingston SQL (11/3/2016)
maria.lindquist (11/2/2016)
The current value is of varchar(7) type.ok, without you answering the rest of my question for expected results on various scenarios...here is shot in the dark :
CREATE TABLE #test(CurrentValue VARCHAR(7));
INSERT INTO #test(CurrentValue) VALUES ('1.66770');
INSERT INTO #test(CurrentValue) VALUES ('-0.0001');
INSERT INTO #test(CurrentValue) VALUES ('-100000');
INSERT INTO #test(CurrentValue) VALUES ('-10.12');
INSERT INTO #test(CurrentValue) VALUES ('0.123');
INSERT INTO #test(CurrentValue) VALUES ('502.321');
INSERT INTO #test(CurrentValue) VALUES ('100000');
SELECT currentvalue,
newvalue = CASE
WHEN LEFT(currentvalue, 1) = '-'
THEN '-'+RIGHT('00000'+REPLACE(REPLACE(currentvalue, '.', ''), '-', ''), 6)
ELSE RIGHT('000000'+REPLACE(currentvalue, '.', ''), 7)
END
FROM #test;
DROP TABLE #test
This solution does not seem to be right.
To me, '0.123' must turn into '0012300', not '0000123'.
And '502.321', '100000' must overflow, as they do not fit the definition.
This should work better:
SELECT REPLACE(
CASE WHEN SIGN(IntValue) = -1 THEN '-' + + STR(ABS(IntValue), 6)
ELSE STR(IntValue, 7) END
, ' ', '0')
FROM (
SELECT CONVERT(INT, CONVERT(DECIMAL(8,5), StringValue)*100000) IntValue
FROM (
SELECT '1.66770'
UNION
SELECT '-0.0001'
UNION
SELECT '0.123'
UNION
SELECT '-10.12'
UNION
SELECT '502.321'
) SD (StringValue)
) DT
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply