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));
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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