InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 16:21:23 GMT20http://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxI confess I haven't thoroughly read this entire thread but I don't think this approach was covered.[code="sql"]DECLARE @Test NUMERIC(5,4) = 1.55SELECT REPLACE(REVERSE(0.+CAST(REVERSE(@Test + 0.) AS FLOAT)) COLLATE Latin1_General_BIN, '.', '')[/code]I used something similar in this thread: [url]http://www.sqlservercentral.com/Forums/Topic1340623-149-1.aspx[/url] and the timing results there were pretty good.Not sure if the extra explicit CAST needed might drag it down though.Thu, 27 Sep 2012 20:28:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]vivekkumar341 (9/27/2012)[/b][hr]Try this.DECLARE @ta VARCHAR(MAX) = '107.3'SELECT SUBSTRING(@ta,0,CHARINDEX('.',@ta,0)) + SUBSTRING(@ta,CHARINDEX('.',@ta,0) + 1,LEN(@ta))[/quote]Why? How is it relevant to this thread? The OP isn't even around any more - he's a tuna fisherman in Madagascar.Thu, 27 Sep 2012 08:00:00 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxTry this.DECLARE @ta VARCHAR(MAX) = '107.3'SELECT SUBSTRING(@ta,0,CHARINDEX('.',@ta,0)) + SUBSTRING(@ta,CHARINDEX('.',@ta,0) + 1,LEN(@ta))Thu, 27 Sep 2012 07:41:44 GMTvivekkumar341http://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]Eugene Elutin (9/27/2012)[/b][hr][quote][b]ChrisM@Work (9/27/2012)[/b][hr][quote][b]Eugene Elutin (9/27/2012)[/b][hr]I have read a spec... :-)...[/quote]Obviously not this OP's spec! The input value is supposedly DECIMAL(5, 4), however this appears to clash with the quoted numbers in the first couple of pages. It's all tosh.[/quote]I did. And what I've said I cannot see a point in what OP trying to do (or at least in what I see everyone trying to achieve). That is one of the last OP posts: [quote]Thanks for your prompt answer. I was looking for something that would get rid of the decimal pointif the number was 10.73 or 10.733 or 10.7333 or 10.7The decimal place being in any positionThanksTed[/quote]I do understand that he wants to be able to remove decimal point from all possible numbers he gave us. What I don't think is useful is to get result as 1073, 10733, 107333 and 107. I cannot see any possible use of the above at all! Instead, (having in mind that his precision is 4), I think it should be something like 107300, 107330, 107333 and 107000.I can be wrong, but would you think of any good use for the first one? The second one is quite common requirement in finance related applications...[/quote]You're quite correct in questioning an OP's business requirement Eugene, because as often as not, that's where they've come unstuck - interpretation. Folks such as yourself with sufficient experience can see through the mess and figure out what the OP really wants to do, then show them the best way to do it.In this case, many of the OP's sample numbers didn't even fit into a NUMERIC(5,1) and as one poster noticed, no rules were provided to deal with trailing 0's - which in most cases were also omitted from the OP's sample data. The nearest I came up with then - and now - is this:[code="sql"]SELECT Input, Step1 = REPLACE(Input,'.',''), Step2 = REPLACE(REPLACE(Input,'.',''),'0',' '), Step3 = RTRIM(REPLACE(REPLACE(Input,'.',''),'0',' ')), Result = REPLACE(RTRIM(REPLACE(REPLACE(Input,'.',''),'0',' ')),' ','0')FROM (SELECT Input = CAST(1.073 AS NUMERIC(5,4))) d[/code]which trims trailing 0's. There's no guarantee that it's a satisfactory algorithm though, because the requirement wasn't ever properly nailed down.This is a lose-lose situation :hehe:Thu, 27 Sep 2012 07:38:09 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]ChrisM@Work (9/27/2012)[/b][hr][quote][b]Eugene Elutin (9/27/2012)[/b][hr]I have read a spec... :-)...[/quote]Obviously not this OP's spec! The input value is supposedly DECIMAL(5, 4), however this appears to clash with the quoted numbers in the first couple of pages. It's all tosh.[/quote]I did. And what I've said I cannot see a point in what OP trying to do (or at least in what I see everyone trying to achieve). That is one of the last OP posts: [quote]Thanks for your prompt answer. I was looking for something that would get rid of the decimal pointif the number was 10.73 or 10.733 or 10.7333 or 10.7The decimal place being in any positionThanksTed[/quote]I do understand that he wants to be able to remove decimal point from all possible numbers he gave us. What I don't think is useful is to get result as 1073, 10733, 107333 and 107. I cannot see any possible use of the above at all! Instead, (having in mind that his precision is 4), I think it should be something like 107300, 107330, 107333 and 107000.I can be wrong, but would you think of any good use for the first one? The second one is quite common requirement in finance related applications...Thu, 27 Sep 2012 07:18:06 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]Eugene Elutin (9/27/2012)[/b][hr]I have read a spec... :-)...[/quote]Obviously not this OP's spec! The input value is supposedly DECIMAL(5, 4), however this appears to clash with the quoted numbers in the first couple of pages. It's all tosh.Thu, 27 Sep 2012 06:27:00 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxI have read a spec... :-)Now, I cannot see any value in a function which removes decimal point in a way it does. It's just my guess, but I'm somehow sure that the number of decimal points needs to be constant. Otherwise, the results are quite useless:12.001 will become 12001and12.99 will become 1299I've tried to think over, but failed to find any good use for this sort of behaviour.Instead I think that the function should be able to remove point and stay with required precision (length of the decimal part), so with required precision of 312.001 will become 12001and12.99 will become 12990and with required precision of 212.001 will become 1200and12.99 will become 1299so, I would just go with something like:[code="sql"]SELECT CAST(@InputAnyDecimal * POWER(10,@RequiredPrecision) AS DECIMAL(38,0)) [/code]You may want to replace DECIMAL(38,0) with INT or BIGINT if it is appropriate in your case. Thu, 27 Sep 2012 06:12:14 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]deyvisonsouto (9/27/2012)[/b][hr]it's just use convert(int, <<value>>)eq:select convert(int,2.5)result:2[/quote]Nope! Miles off. Did you read the spec?Thu, 27 Sep 2012 05:23:25 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxit's just use convert(int, <<value>>)eq:select convert(int,2.5)result:2Thu, 27 Sep 2012 01:53:15 GMTdeyvisonsoutohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxI was wondering the usefulness myself. Unless you store elsewhere data to put back in the decimal, I can't imagine the number being very useful. But I'm sure somebody can think of a use.Even though the number generated seems to have no apparent use, that doesn't mean the challenge wasn't fun.------And not to toot my own horn, but with my solution you could easily capture and store the data needed to put the decimal place back in. :)Thu, 11 Oct 2007 08:10:48 GMTS. Froghttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxHeh... yeah... several solutions similar to that were posted.The remaining question I have is, what will this be used for? Without knowing were the decimal point WAS... the information is totally useless.Thu, 11 Oct 2007 06:02:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxSorry the variable value got stripped out... here you goIf you're like me and want to clean up the decimal places during your select statement, you can use the following function set:Replace(Left(decimalfield as varchar))), '.', '') as YourAliasThis takes care of 0.00 values as well... hope it helps!- Aaron CurtisThu, 11 Oct 2007 00:09:41 GMTAaron Curtishttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxIf you're like me and want to clean up the decimal places during your select statement, you can use the following function set:Replace(Left( as varchar))), '.', '') as YourAliasThis takes care of 0.00 values as well... hope it helps!- Aaron CurtisThu, 11 Oct 2007 00:07:38 GMTAaron Curtishttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxOK, based on the original question posed, your solution is valid.I guess I got carried away and lost sight of the "requirements" :blush:Tue, 09 Oct 2007 01:24:40 GMTJohn te Lintelohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[code]DECLARE @Sample TABLE (d DECIMAL(5, 4))DECLARE @Original DECIMAL(6, 4)SET @Original = 0.0WHILE @Original < 10.0BEGININSERT @SampleSELECT @OriginalSET @Original = @Original + 0.0001ENDSELECT d AS OrignalValue,REPLACE(REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(STR(d, 15, 5), ' ', '#'), '0', ' ')), ' ', '0'), '#', ''), '.', ''),CAST(10000 * d AS INT)FROM @SampleORDER BY d[/code]Tue, 09 Oct 2007 01:16:37 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxOP (Ted Montoya) wrote 9/26/2007 10:04 PM that datatype is DECIMAL(5, 4).All values I tested my code with works...Tue, 09 Oct 2007 01:06:01 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxSure, the value 9876504321.1203456 as used in the examples above. Even after changing the code to use DECIMAL(38, 12) the result is 98765043211203461 instead of 98765043211203456.Tue, 09 Oct 2007 00:55:31 GMTJohn te Lintelohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxDo you have an example?Tue, 09 Oct 2007 00:49:16 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]Peter Larsson (10/8/2007)[/b][hr]DECLARE@Original DECIMAL(10, 5)SET@Original = 1078.734SELECT@Original,REPLACE(REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(STR(@Original, 15, 5), ' ', '#'), '0', ' ')), ' ', '0'), '#', ''), '.', '')[/quote]Unfortunately the STR function takes a float as a parameter, losing the accuracy of a decimal.Mon, 08 Oct 2007 17:34:08 GMTJohn te Lintelohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxDECLARE @Original DECIMAL(10, 5)SET @Original = 1078.734SELECT @Original, REPLACE(REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(STR(@Original, 15, 5), ' ', '#'), '0', ' ')), ' ', '0'), '#', ''), '.', '')Mon, 08 Oct 2007 09:16:59 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxFixed for 12345.0Declare @Original decimal(25,10)Set @Original = 12345.0 -- 09876504321.120345678IF (@Original-(cast(@Original as bigint))) = 0select cast(@Original as bigint)ELSEselect cast(@Original*power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0))Not quite as elegant as I had hoped, but it works.-S.F.Mon, 08 Oct 2007 08:57:36 GMTS. Froghttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxThe error in Jeff's code is easy to fix by setting @Result to FLOOR(@Original) before the loop, however this makes it identical to test 2 except for the fact that the two variables are set in a single statement instead of two.The test harness is inaccurate as @Original and @Result need to be reset at the start of each test loop otherwise the algorithms in tests 2 and 3 don't get run.Finally, in test 1 the result is a varchar that is stored in a decimal variable so an implicit conversion is performed, only to be converted back to a varchar for printing. I changed the test harness to store the result in a varchar.The results are interesting...I also tried the code by S. Frog however it doesn't work for the value 12345.0 (same problem as Jeff's original code).--=====================================================================-- Setup the environment common to all tests--=====================================================================--===== Suppress the auto-display of rowcountsSET NOCOUNT ON--===== Declare the necessary local variablesDECLARE @UnolteredOriginal DECIMAL(38,12) --The original decimal valueDECLARE @Original DECIMAL(38,12) --The original decimal valueDECLARE @Result DECIMAL(38, 0) --The desired resultDECLARE @ResultStr VARCHAR(200) --The desired resultDECLARE @MaxLoop INT --Number of times to perform the loopDECLARE @Counter INT --General purpose loop counterDECLARE @StartTime DATETIME --To measure duration for each test--===== PresetsSET @UnolteredOriginal = 09876504321.1203456SET @MaxLoop = 1000000PRINT 'Original value as stored...'PRINT @UnolteredOriginalPRINT REPLICATE('-',78)--=====================================================================-- Test Method 1: Character based manipulation--=====================================================================PRINT 'Test Method 1: Character based manipulation'--===== PresetsSET @Original = @UnolteredOriginalSET @Counter = 1SET @StartTime = GETDATE()--===== Run the testWHILE @Counter <= @MaxLoopBEGIN --Test Code---------------------------------------------------------------SET @ResultStr = REPLACE(RTRIM(REPLACE(REPLACE(CONVERT(VARCHAR,@Original ),'.','' ),'0',' ')),' ','0')---------------------------------------------------------------SET @Counter = @Counter + 1END--===== Display the calculated duration per loopPRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))+ ' Microseconds per loop'--===== Print the resultPRINT @ResultStr + ' Result'PRINT REPLICATE('-',78)--=====================================================================-- Test Method 2: Typical Numeric based loop--=====================================================================SET @Result = NULLPRINT 'Test Method 2: Numeric based loop'--===== PresetsSET @Counter = 1SET @StartTime = GETDATE()--===== Run the testWHILE @Counter <= @MaxLoopBEGIN --Test CodeSELECT @Original = @UnolteredOriginal, @Result = FLOOR(@Original)---------------------------------------------------------------SET @Result = FLOOR(@Original);WHILE @Result < @OriginalBEGINSET @Original = @Original * 10SET @Result = FLOOR(@Original);END---------------------------------------------------------------SET @Counter = @Counter + 1END--===== Display the calculated duration per loopPRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))+ ' Microseconds per loop'--===== Print the resultPRINT CAST(@Result AS VARCHAR(40)) + ' Result'PRINT REPLICATE('-',78)--=====================================================================-- Test Method 3: Numeric based loop--=====================================================================SET @Result = NULLPRINT 'Test Method 3: Not-so-typical Numeric based loop'--===== PresetsSET @Counter = 1SET @StartTime = GETDATE()--===== Run the testWHILE @Counter <= @MaxLoopBEGIN --Test CodeSELECT @Original = @UnolteredOriginal, @Result = FLOOR(@Original)---------------------------------------------------------------WHILE ISNULL(@Result, 0) < @OriginalSELECT @Original = @Original * 10, @Result = FLOOR(@Original)---------------------------------------------------------------SET @Counter = @Counter + 1END--===== Display the calculated duration per loopPRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40))+ ' Microseconds per loop'--===== Print the resultPRINT CAST(@Result AS VARCHAR(40)) + ' Result'PRINT REPLICATE('-',78)Original value as stored...9876504321.120345600000------------------------------------------------------------------------------Test Method 1: Character based manipulation7.843000000000 Microseconds per loop98765043211203456 Result------------------------------------------------------------------------------Test Method 2: Numeric based loop32.410000000000 Microseconds per loop98765043211203456 Result------------------------------------------------------------------------------Test Method 3: Numeric based loop25.733000000000 Microseconds per loop98765043211203456 Result------------------------------------------------------------------------------Original value as stored...12345.000000000000------------------------------------------------------------------------------Test Method 1: Character based manipulation7.330000000000 Microseconds per loop12345 Result------------------------------------------------------------------------------Test Method 2: Numeric based loop4.733000000000 Microseconds per loop12345 Result------------------------------------------------------------------------------Test Method 3: Numeric based loop4.593000000000 Microseconds per loop12345 Result------------------------------------------------------------------------------Sun, 07 Oct 2007 19:58:29 GMTJohn te Lintelohttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxAnd for those of you who, unlike me, remember that you can use bigint if int is too restrictive, this works as well as my last post, and doesn't have the left() or charindex() in it. Which makes this one, in my opinion, better than my last one.Declare @Original decimal(25,10)Set @Original = 09876504321.120345678 -- 0987654321123456.012345678select cast(@Original*power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0))-- Again, stepping it throughselect @Original, -- Original number left(@Original,charindex('.',cast(@Original as varchar(30)))), -- Original number without the decimals @Original-(cast(@Original as bigint)), -- Just the decimals Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))), -- Just the decimals, but reversed cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)), -- Get rid of unneeded 0s len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0))), -- How many digits, will use this as an exponent power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))), -- What do I need to multiply the original number by? cast(@Original*power(10,len(cast(Reverse(cast(@Original-(cast(@Original as bigint)) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0)) --Eureka!--SFFri, 05 Oct 2007 13:18:44 GMTS. Froghttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxWell, it's not quite ideal as I can't use numbers with 10 decimal places. My method is restricted to 9 digits after the decimal place, but that isn't too shabby in my opinion.Here is my solution (similar to my earlier ones, but this, I believe, actually works):Declare @Original decimal(25,10)Set @Original = 09876504321.120345678 -- 0987654321123456.012345678select cast(@Original*power(10,len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0))-- Below steps out what the above is doingselect @Original, -- Original numberleft(@Original,charindex('.',cast(@Original as varchar(30)))), -- Original number without the decimalscast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14)), -- Just the decimalsReverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))), -- Just the decimals, but reversedcast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)), -- Get rid of unneeded 0slen(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0))), -- How many digits, will use this as an exponentpower(10,len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)))), -- What do I need to multiply the original number by?cast(@Original*power(10,len(cast(Reverse(cast(@Original-left(@Original,charindex('.',cast(@Original as varchar(30)))) as decimal(25,14))) as decimal(25,0)))) as decimal(25,0)) --Eureka!Any comments? Criticisms?Fri, 05 Oct 2007 12:54:04 GMTS. Froghttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxWow... I made a mess of that one :blush: It's only fast if it works right, huh :DFolks, don't use method 3... Tomm's right... I've got a nasty bug in it... I see what I can do to fix it, but right now it's wrong and so am I :blush:...Thu, 04 Oct 2007 17:37:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxThanks for the heads up, Tomm... I'll check it out...Thu, 04 Oct 2007 17:21:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxOh, sorry. :blush: I forgot to mention that it would seem to work because @Result is left at the correct answer from the method that came before it. So when working with whole numbers, Method 3 never enters the loop because @Result starts out equal to @Original. However, it you reset @Result to 0 or null between the two methods, you will see what I mean. Actually, you should reset @Original also as method 2 modifies it.Thu, 04 Oct 2007 10:21:27 GMTTomm Carrhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]Tomm Carr (10/3/2007)[/b][hr]Sorry, Jeff. You got the third method to operate a little faster by removing the setup just before entering the loop. This is a crucial step. Without it, the method gives the correct answer only for fractional values. For whole numbers (such as 12345.0) the method, by performing the multiplication first, gives an answer that is off by a factor of 10 (123450) while methods 1 & 2 give the correct answer.It's frustrating, I know. We could write some [i]smoking[/i] code if only the users would be satisfied with getting a correct answer [i]most of the time[/i]. That silly [i]every single time[/i] constraint can be a real nuisance.:rolleyes:[/quote]What in the hell are you talking about?Original value as stored...12345.000000000000------------------------------------------------------------------------------Test Method 1: Character based manipulation20.126000000000 Microseconds per loop12345 Result------------------------------------------------------------------------------Test Method 2: Typical Numeric based loop10.390000000000 Microseconds per loop12345 Result------------------------------------------------------------------------------[b]Test Method 3: Not-so-typical Numeric based loop8.406000000000 Microseconds per loop12345 Result[/b]------------------------------------------------------------------------------Wed, 03 Oct 2007 23:06:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxOK. I've gotten around my the problem with my earlier submission where I was restricted to 9 digits to the left of the decimal, and 6 digits to the right of the decimal.What I also like about this is that is is a mathematical solution. No turning numbers into characters.Let me know what you think:Declare @Original decimal(25,10)Set @Original = 09876504321.1203456select cast(@Original*power(10,len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1) as decimal(20,0))-- In case you want a readable version of what the above is doing:select @Original, --Original numbercast(@Original as decimal(15,0)), --Original number without the decimalscast(@Original-cast(@Original as decimal(15,0)) as float(53)), --Just the decimalslen(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1, --How many decimal placespower(10,len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1), --10 to the power of the number of decimal placescast(@Original*power(10,len(cast(@Original-cast(@Original as decimal(15,0)) as float(53)))-1) as decimal(20,0)) --Multiply away the decimals from the original********EDIT: Dang it. Hold on a sec. I have a problem with my count of the decimal places. Working on a fix.EDIT #2: Ah the best laid plans... This still has a restriction of 6 decimal places. Any more than that and I can't properly calculate the power of 10 I need. Sorry for the false alarm. --SFWed, 03 Oct 2007 16:02:26 GMTS. Froghttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxSorry, Jeff. You got the third method to operate a little faster by removing the setup just before entering the loop. This is a crucial step. Without it, the method gives the correct answer only for fractional values. For whole numbers (such as 12345.0) the method, by performing the multiplication first, gives an answer that is off by a factor of 10 (123450) while methods 1 & 2 give the correct answer.It's frustrating, I know. We could write some [i]smoking[/i] code if only the users would be satisfied with getting a correct answer [i]most of the time[/i]. That silly [i]every single time[/i] constraint can be a real nuisance.:rolleyes:Wed, 03 Oct 2007 11:00:30 GMTTomm Carrhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxYeah, huh? Using loops comes highly unnatural to me. :D Goes against my data-troll grain.The other place I saw it was with a splitter function that used a WHILE loop. The way the guy did it, it actually beat the Tally table method for single rows... still couldn't beat the Tally table method for splitting a whole table with no function, but I was definitely impressed.Tue, 02 Oct 2007 22:44:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxAs I sai Jeff, there's something to be said for ingenuity and straight math operations. It's worth mentioning that math ops will almost always beat the crap out of 15 different conversions / string ops like I had to do to get the results. It's just common sens. :w00t:But I agree that it seems a bit unnatural. It's like having to say that a cursor is good in production code :hehe:.Tue, 02 Oct 2007 22:33:21 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote]You can touch it. It won't hurt you.[/quote]Heh... and folks think Serqiy is a smartass??? :laugh:Anyway, point well taken and I guess I have to eat a little crow on this one :blush: That's one of the few times I've seen a loop beat an all function solution and for the reasons you stated...But, if you're going to write a loop, would you at least write one that's [i]fast[/i]? :PHere's the hands-free no-modification-required test harness I built from your code example... take a peek at the 3rd method :alien:--=====================================================================-- Setup the environment common to all tests--=====================================================================--===== Suppress the auto-display of rowcounts SET NOCOUNT ON--===== Declare the necessary local variablesDECLARE @Original DECIMAL(38,12) --The original decimal valueDECLARE @Result DECIMAL(38, 0) --The desired resultDECLARE @MaxLoop INT --Number of times to perform the loopDECLARE @Counter INT --General purpose loop counterDECLARE @StartTime DATETIME --To measure duration for each test--===== Presets SET @Original = 09876504321.1203456 SET @MaxLoop = 1000000 PRINT 'Original value as stored...' PRINT @Original PRINT REPLICATE('-',78)--=====================================================================-- Test Method 1: Character based manipulation--===================================================================== PRINT 'Test Method 1: Character based manipulation'--===== Presets SET @Counter = 1 SET @StartTime = GETDATE()--===== Run the test WHILE @Counter <= @MaxLoop BEGIN --Test Code --------------------------------------------------------------- SET @Result = REPLACE(RTRIM(REPLACE(REPLACE(CONVERT(VARCHAR,@Original ),'.','' ),'0',' ')),' ','0') --------------------------------------------------------------- SET @Counter = @Counter + 1 END--===== Display the calculated duration per loop PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40)) + ' Microseconds per loop'--===== Print the result PRINT CAST(@Result AS VARCHAR(40)) + ' Result' PRINT REPLICATE('-',78)--=====================================================================-- Test Method 2: Typical Numeric based loop--===================================================================== PRINT 'Test Method 2: Typical Numeric based loop'--===== Presets SET @Counter = 1 SET @StartTime = GETDATE()--===== Run the test WHILE @Counter <= @MaxLoop BEGIN --Test Code --------------------------------------------------------------- SET @Result = FLOOR(@Original); WHILE @Result < @Original BEGIN SET @Original = @Original * 10 SET @Result = FLOOR(@Original); END --------------------------------------------------------------- SET @Counter = @Counter + 1 END--===== Display the calculated duration per loop PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40)) + ' Microseconds per loop'--===== Print the result PRINT CAST(@Result AS VARCHAR(40)) + ' Result' PRINT REPLICATE('-',78)--=====================================================================-- Test Method 3: Not-so-typical Numeric based loop--===================================================================== PRINT 'Test Method 3: Not-so-typical Numeric based loop'--===== Presets SET @Counter = 1 SET @StartTime = GETDATE()--===== Run the test WHILE @Counter <= @MaxLoop BEGIN --Test Code --------------------------------------------------------------- WHILE ISNULL(@Result,0) < @Original SELECT @Original = @Original * 10, @Result = FLOOR(@Original) --------------------------------------------------------------- SET @Counter = @Counter + 1 END--===== Display the calculated duration per loop PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE())*1000.0/@MaxLoop AS VARCHAR(40)) + ' Microseconds per loop'--===== Print the result PRINT CAST(@Result AS VARCHAR(40)) + ' Result' PRINT REPLICATE('-',78)[b]...and, here's the results...[/b]Original value as stored...9876504321.120345600000------------------------------------------------------------------------------Test Method 1: Character based manipulation22.436000000000 Microseconds per loop98765043211203456 Result------------------------------------------------------------------------------Test Method 2: Typical Numeric based loop11.220000000000 Microseconds per loop98765043211203456 Result------------------------------------------------------------------------------Test Method 3: Not-so-typical Numeric based loop8.436000000000 Microseconds per loop98765043211203456 Result------------------------------------------------------------------------------Tue, 02 Oct 2007 22:27:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxHere's what I used:[code]-- Initialization common to both methods.Declare @Original decimal( 38, 12 ), @Result decimal( 38, 0 ); -- Doesn't need scale, only precisionSet @Original = 09876504321.1203456; -- This would be, say, an input parameterDeclare @MaxLoop int;set @MaxLoop = 1000000;while @MaxLoop > 0 begin -- Uncomment one method or the other. -- Method 1:-- Set @Result = Replace( RTrim( Replace( Replace( Convert( varchar, @Original ), '.', '' ), '0', ' ' ) ), ' ', '0' ); -- Method 2:-- Set @Result = Floor( @Original );-- While @Result < @Original-- begin-- Set @Original = @Original * 10;-- Set @Result = floor( @Original );-- end--while Set @MaxLoop = @MaxLoop - 1; end--loopselect @Result as Result;[/code]I used the Client Processing Time in Statistics. There is no I/O except to display the result at the end so essentially all time is processing time.Tue, 02 Oct 2007 12:20:46 GMTTomm Carrhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxQuoting myself : Sorry Tomm, I had missed your post. Your solution is much much workable than mine.However, I too would like to see the whole test. Math operations are most of the time faster than string ops. This seems to fall under the same category.Mon, 01 Oct 2007 20:06:08 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxPost the entire test harness so we can do the same test, Tom ;)Mon, 01 Oct 2007 18:33:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxJust for grins, I compared two methods.Method 1: No (explicit) looping.[b]Set @Result = Replace( RTrim( Replace( Replace( Convert( varchar, @Original ), '.', '' ), '0', ' ' ) ), ' ', '0' );[/b]and Method 2: looping[b] While @Result < @Working begin Set @Working = @Working * 10; Set @Result = floor( @Working ); end--while[/b]Both produced the same results but Method 2 executed in about 1/3 the time of Method 1 (9.3 microsecs vs. 3.0 microsec).Mon, 01 Oct 2007 17:43:08 GMTTomm Carrhttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxI love it... this is why we need that many members here!Mon, 01 Oct 2007 15:23:38 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspxNow keep in mind, I'm still kind of a newbie, but this seems to work ok. Though it gacks if you use more than 9 digits before or more than 6 digits after the decimal place. But I'm sure a more experienced person can point out where that can be fixed.Declare @num float(38)Set @num = 987654321.123456select @num*(power(10,(len(@num-(cast(@num as int)))-2)))If you want to step out each action, this might help:select num, -- original numbercast(@num as int), -- original number without decimals@num-cast(@num as int), -- just the decimalslen(@num-cast(@num as int))-2, -- how many decimal placespower(10,(len(@num-(cast(@num as int)))-2)), -- 10 to the power of the number of decimal places@num*(power(10,(len(@num-(cast(@num as int)))-2))) -- I'm gonna wash that decimal right out of my hair...What do you think - good, bad, or SQL isn't for people with Anthro degrees?--S. FrogMon, 01 Oct 2007 15:07:28 GMTS. Froghttp://www.sqlservercentral.com/Forums/Topic403180-338-1.aspx[quote][b]Jeff Moden (9/29/2007)[/b][hr]Did someone say "loop"??? :blink: On a single row????? :sick:[/quote]Don't let (y)our antipathy toward row looping turn you away from all looping. Many times it is appropriate, especially when there is no data access within the loop. It is strictly a mathematical loop. You can touch it. It won't hurt you.[quote]Using a previous example where 5 decimal places are required to be padded with zeros...[/quote]But there was no requirement that it be padded with zeros. In the original examples, there were no trailing zeros.[quote]Why do you need a loop????[/quote]Even your solution has a loop. The Replace function must loop through each character position in the string. Just because a loop has been abstracted from sight doesn't mean it isn't still there. My loop only repeats for each significant digit to the right of the decimal point. So, I have to loop an average of about half as many times as Replace.Mon, 01 Oct 2007 11:04:09 GMTTomm Carr