November 7, 2016 at 10:34 am
Hi all,
Looking for some quick help with a bit of SQL. The best way I can describe it is that I'm trying to add 1 to the least significant digit in a given input number. Some examples:
1.2 would become 1.3 (adding .1)
6.25 would become 6.26 (adding .01)
9 would become 10 (adding 1)
3.099 would become 3.1 (adding .001)
I hope I've explained that well. Any nifty SQL tricks or hints are appreciated!
November 7, 2016 at 10:56 am
BowlOfCereal (11/7/2016)
Hi all,Looking for some quick help with a bit of SQL. The best way I can describe it is that I'm trying to add 1 to the least significant digit in a given input number. Some examples:
1.2 would become 1.3 (adding .1)
6.25 would become 6.26 (adding .01)
9 would become 10 (adding 1)
3.099 would become 3.1 (adding .001)
I hope I've explained that well. Any nifty SQL tricks or hints are appreciated!
I have some rather ugly SQL for you which seems to work:
DECLARE @x SQL_VARIANT = 3.099;
SELECT
@x
, Res = CAST(@x AS DECIMAL(18, 6)) + 1.0 / POWER(10, CAST(SQL_VARIANT_PROPERTY(@x, 'Scale') AS INT));
November 7, 2016 at 11:13 am
Wow! Thanks for the quick and helpful reply. The code isn't quite perfect (3.0 returns 3.1, for example), but it gives me a great head start.
November 7, 2016 at 11:21 am
BowlOfCereal (11/7/2016)
Wow! Thanks for the quick and helpful reply. The code isn't quite perfect (3.0 returns 3.1, for example), but it gives me a great head start.
No problem.
If 1.2 becomes 1.3, then 3.0 becoming 3.1 seems fairly reasonable to me, but I understand why you would want 3.0 to be treated as 3.
November 7, 2016 at 11:47 am
Phil's solution is brilliant (this was a tricky problem that I could not solve).
Based on what you said - 4.0 should be 4.1 but if you want 4.0 to become 5 you could do something like this (using Phil's solution as a start):
DECLARE @x SQL_VARIANT = 3.0;
SELECT Res = IIF
(
PATINDEX('%.%[^0]%', CAST(@x AS varchar(25))) > 0,
CAST(@x AS DECIMAL(18, 6)) + 1.0 / POWER(10, CAST(SQL_VARIANT_PROPERTY(@x, 'Scale') AS INT)),
CAST(@x AS DECIMAL(18, 6)) + 1.0
);
-- Itzik Ben-Gan 2001
November 7, 2016 at 11:56 am
Thank you as well! But grrrr..... now I'm running into a new problem; the data I'm working with is in a varchar(max) column, and I'm getting the error "Operand type clash: varchar(max) is incompatible with sql_variant" trying to implement this logic. I was trying to keep the post simple -- that's what I get for leaving out details! This is a varchar(max) column that contains some values like '>3.245' or '>6.2'. Our business has requested we convert these to a numeric value in a separate column, stripping off the '>' and incrementing the number using the logic I described.
Here's some code to populate a temp table similar to what I'm querying (leaving out the trivial '>' part); the operand type clash error results from the select statement at the bottom.
create table #mytab (origval varchar(max))
insert #mytab values
('1.2'), --should convert to 1.3
('6.25'), --should convert to 6.26
('9'), --should convert to 10
('3.099'), --should convert to 3.1
('5.0') --should convert to 6
select origval,
Res = CAST(origval AS DECIMAL(18, 6)) + 1.0 / POWER(10, CAST(SQL_VARIANT_PROPERTY(origval, 'Scale') AS INT))
from #mytab
November 7, 2016 at 12:18 pm
This might work.
create table #mytab (origval varchar(max))
insert #mytab values
('1.2'), --should convert to 1.3
('6.25'), --should convert to 6.26
('9'), --should convert to 10
('3.099'), --should convert to 3.1
('5.0'), --should convert to 6
('10') --should convert to 10
select origval,
origval + CASE WHEN origval NOT LIKE '%.%[^0]%' THEN digit / 10 ELSE digit END
from #mytab
CROSS APPLY( SELECT POWER(CAST(10 AS float), -(LEN(origval) - PATINDEX( '%[1-9]%', REVERSE(origval)) + 1 - CHARINDEX( '.', origval + '.')))) x(digit);
GO
DROP table #mytab
EDIT: Changed the CASE clause to prevent calculating the value twice.
November 7, 2016 at 12:52 pm
Luis Cazares (11/7/2016)
This might work.
create table #mytab (origval varchar(max))
insert #mytab values
('1.2'), --should convert to 1.3
('6.25'), --should convert to 6.26
('9'), --should convert to 10
('3.099'), --should convert to 3.1
('5.0'), --should convert to 6
('10') --should convert to 10
select origval,
origval + CASE WHEN origval NOT LIKE '%.%[^0]%' THEN digit / 10 ELSE digit END
from #mytab
CROSS APPLY( SELECT POWER(CAST(10 AS float), -(LEN(origval) - PATINDEX( '%[1-9]%', REVERSE(origval)) + 1 - CHARINDEX( '.', origval + '.')))) x(digit);
GO
DROP table #mytab
EDIT: Changed the CASE clause to prevent calculating the value twice.
Thanks! It's still not quite perfect ('10' converts to '20', for example), but I think I understand the logic enough to tweak it how I need.
Other ideas always welcome!
November 7, 2016 at 12:58 pm
You might just need to change "digit / 10" to "1".
November 7, 2016 at 1:14 pm
Since you want the final result as a numeric value, it doesn't make sense to manipulate the string. Try the following:
SELECT m.origval, n.comp_val
FROM #mytab m
CROSS APPLY (
SELECT TOP (1) origval + POWER(CAST(10 AS DECIMAL(18,6)), -n) AS comp_val
FROM ( VALUES(-2), ( -1), ( 0), ( 1), ( 2) ) n(n)
WHERE origval = ROUND(origval, n)
ORDER BY n
) n
You may want to use a tally table instead of hard-coding the range.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 7, 2016 at 2:14 pm
Simple "set" based approach
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#mytab') IS NOT NULL DROP TABLE #mytab;
create table #mytab (origval varchar(max))
insert #mytab values
('1.2'), --should convert to 1.3
('6.25'), --should convert to 6.26
('9'), --should convert to 10
('3.099'), --should convert to 3.1
('5.0') --should convert to 6
;
SELECT
CONVERT(DECIMAL(18,6),X.origval,0) AS ORIGINAL_VALUE
,CONVERT(DECIMAL(18,6),X.origval,0) + MIN(XX.AV) AS DESIRED_VALUE
FROM #mytab X
CROSS APPLY ( SELECT SIGN(CONVERT(DECIMAL(18,6),X.origval,0) ) * 1.0 UNION ALL
SELECT SIGN(CONVERT(DECIMAL(18,6),X.origval,0) % 1.0 ) * 0.1 UNION ALL
SELECT SIGN(CONVERT(DECIMAL(18,6),X.origval,0) % 0.1 ) * 0.01 UNION ALL
SELECT SIGN(CONVERT(DECIMAL(18,6),X.origval,0) % 0.01 ) * 0.001 UNION ALL
SELECT SIGN(CONVERT(DECIMAL(18,6),X.origval,0) % 0.001) * 0.0001
) XX(AV)
WHERE XX.AV > 0.0
GROUP BY CONVERT(DECIMAL(18,6),X.origval,0);
Output
ORIGINAL_VALUE DESIRED_VALUE
---------------- ---------------
1.200000 1.3000000000
3.099000 3.1000000000
5.000000 6.0000000000
6.250000 6.2600000000
9.000000 10.0000000000
November 8, 2016 at 1:46 pm
drew.allen (11/7/2016)
Since you want the final result as a numeric value, it doesn't make sense to manipulate the string. Try the following:
SELECT m.origval, n.comp_val
FROM #mytab m
CROSS APPLY (
SELECT TOP (1) origval + POWER(CAST(10 AS DECIMAL(18,6)), -n) AS comp_val
FROM ( VALUES(-2), ( -1), ( 0), ( 1), ( 2) ) n(n)
WHERE origval = ROUND(origval, n)
ORDER BY n
) n
You may want to use a tally table instead of hard-coding the range.
Drew
Very cool! This logic is great for my purposes, with one minor bug fix. I originally included all values between -10 and 10 in the tally table, and found that the logic returned incorrect results in a few cases (10, 500). Experimenting, I found that removing the negative numbers from the tally table seems to have fixed the problem (the tally table runs 0 through 10 now).
I'd love your thoughts on that. Frankly, I don't understand the code fully yet, and I'm not sure my "fix" is correct. Did you find you originally needed to include the negative numbers (-2 and -1)? Do you see any problem with limiting the range to 0 through 10? Thanks so much for the help!
November 8, 2016 at 2:05 pm
BowlOfCereal (11/8/2016)
drew.allen (11/7/2016)
Since you want the final result as a numeric value, it doesn't make sense to manipulate the string. Try the following:
SELECT m.origval, n.comp_val
FROM #mytab m
CROSS APPLY (
SELECT TOP (1) origval + POWER(CAST(10 AS DECIMAL(18,6)), -n) AS comp_val
FROM ( VALUES(-2), ( -1), ( 0), ( 1), ( 2) ) n(n)
WHERE origval = ROUND(origval, n)
ORDER BY n
) n
You may want to use a tally table instead of hard-coding the range.
Drew
Very cool! This logic is great for my purposes, with one minor bug fix. I originally included all values between -10 and 10 in the tally table, and found that the logic returned incorrect results in a few cases (10, 500). Experimenting, I found that removing the negative numbers from the tally table seems to have fixed the problem (the tally table runs 0 through 10 now).
I'd love your thoughts on that. Frankly, I don't understand the code fully yet, and I'm not sure my "fix" is correct. Did you find you originally needed to include the negative numbers (-2 and -1)? Do you see any problem with limiting the range to 0 through 10? Thanks so much for the help!
Your fix seems correct. The negative numbers shouldn't be there to get the LSD (which I had to Google to remember how it is defined). The range 0 to 10 will allow up to 10 decimal places, it won't return rows if more decimal places are used.
The code is returning the original value + 10^-n, only when the rounded value to n decimal places is equal to the original value. It only returns one row ordered in a way that will return the LSD instead of a smaller value. I hope that explains it. If it doesn't, ask whatever you need.
November 8, 2016 at 2:19 pm
BowlOfCereal (11/8/2016)
drew.allen (11/7/2016)
Since you want the final result as a numeric value, it doesn't make sense to manipulate the string. Try the following:
SELECT m.origval, n.comp_val
FROM #mytab m
CROSS APPLY (
SELECT TOP (1) origval + POWER(CAST(10 AS DECIMAL(18,6)), -n) AS comp_val
FROM ( VALUES(-2), ( -1), ( 0), ( 1), ( 2) ) n(n)
WHERE origval = ROUND(origval, n)
ORDER BY n
) n
You may want to use a tally table instead of hard-coding the range.
Drew
Very cool! This logic is great for my purposes, with one minor bug fix. I originally included all values between -10 and 10 in the tally table, and found that the logic returned incorrect results in a few cases (10, 500). Experimenting, I found that removing the negative numbers from the tally table seems to have fixed the problem (the tally table runs 0 through 10 now).
I'd love your thoughts on that. Frankly, I don't understand the code fully yet, and I'm not sure my "fix" is correct. Did you find you originally needed to include the negative numbers (-2 and -1)? Do you see any problem with limiting the range to 0 through 10? Thanks so much for the help!
The negative numbers handle cases where the least significant digit falls to the left of the decimal point (e.g., 1000.00). You're using a definition where the first digit to the left of the decimal point is always significant.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 8, 2016 at 5:58 pm
Thanks again for the info and help, all!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy