June 6, 2022 at 8:17 pm
Hi all,
I have derived column in the package (eight-bite integer value):
(Test_column % 1000000000).
Example,
we have a code 111100000001234566. Using this expression we received 1234566.
But now I need also fetch the first 4 digit and added this expression (Test_column % 1000000000).
So, I want to receive this value 11111234566.
Could you please help me to parse the value?
Thank you.
June 6, 2022 at 8:37 pm
I take it that the zero's between the two obvious parts could be consumed by an increasing value of the right part or will it ALWAYS be 7 digits (no more and no less) for a total of always 11 digits? I ask because that while conversion to a string and grabbing the parts would work, it would be relatively slow. Using integer divide to isolate the left part and integer modulus would make it faster.
I also think that concatenating the two parts is a mistake for the future. Save that for "presentation time". Don't store the concatenated 2 parts in the database.
--Jeff Moden
June 6, 2022 at 10:35 pm
Here's the code to get the two values as separate integers. I've also included code to concatenate them as a string. I still don't believe that joining them together is a good idea for anything other than display purposes.
DECLARE @Test BIGINT = 111100000001234566
,@Parse BIGINT = 100000000000000
;
--===== Parsing the data as separate BIGINTs,
-- which is what I recommend for saving.
SELECT First4Digits = @Test/@Parse
,EveryThingElse = @Test%@Parse
;
--===== If you''ve really got to have them "concatenated"
-- and in the absence of the other info I asked for
-- to try to keep this all as fast Integer Math,
-- this will convert both results to a string and
-- mash them together as a single string.
-- Again, I don''t recommend denormalizing the values
-- like this.
SELECT TheString = CONCAT(@Test/@Parse,@Test%@Parse)
;
--Jeff Moden
June 6, 2022 at 10:55 pm
with cte as
(
SELECT CONVERT(bigint, 111100000001234566) x
)
select x, convert(bigint,left(x,4) + right(x, 7))
from cte
June 7, 2022 at 12:03 am
Well blow me down. I took my other post I had here down. The string method Jonathan posted beats even the pure integer math in this case.
Here's the test table I made if someone wants to play. The pure integer math method only beats Jonathan's method if you don't recombine the two part, which is what I recommend but ya gotta do what ya gotta do.
DROP TABLE IF EXISTS #TestTable;
GO
SELECT SplitMe = CONVERT(BIGINT,ABS(CHECKSUM(NEWID())%10000)*100000000000000+ABS(CHECKSUM(NEWID())%10000000))
INTO #TestTable
FROM dbo.fnTally(1,1000000)
;
GO
You can get the fnTally() function from the similarly named link in my signature line below.
--Jeff Moden
June 7, 2022 at 12:46 am
Jonathan AC Roberts wrote:with cte as
(
SELECT CONVERT(bigint, 111100000001234566) x
)
select x, convert(bigint,left(x,4) + right(x, 7))
from cteThat's works only if the right part is always 7 and I suspect it will not. It also has string conversions, which are typically slower than pure math. Without being guaranteed how long the right side will be, here's a pure math solution to figure out how long it is without string conversions but I suspect the use of LOG and FLOOR will make it a bit slower. I haven't tested with method for performance.
DECLARE @Test BIGINT = 111100000001234566
,@Parse BIGINT = 100000000000000
;
--===== Parsing the data as separate BIGINTs,
-- which is what I recommend for saving.
SELECT First4Digits = @Test/@Parse
,EveryThingElse = @Test%@Parse
;
--===== I haven''t tested for performance (see notes above) but here''s a pure
-- math solution if the significant length of the right side is not known.
SELECT @Test/@Parse*POWER(10,FLOOR(LOG(@Test%@Parse)/LOG(10)+1))+@Test%@Parse
;I still think it's a mistake to recombine the values in any fashion
Yes, I hadn't noticed that it was selecting the last 9 digits and removing the leading zeros from it. So it doesn't seem right to concatenate the values. But I would have thought that simple string operations should be faster than complex math operations like POWER.
June 7, 2022 at 12:50 am
If there's a guaranteed 4 significant digits to the left and no more than 7 digits to the right, this will also work. It still has the issue of being recombined, though. It's just a touch faster the Jonathan's code, which makes the same assumptions.
SELECT SplitMe, Result = STUFF(SplitMe,5,7,'')
FROM #TestTable;
--Jeff Moden
June 7, 2022 at 1:33 am
Apologies for the numerous posts. I just find problems like this to be interesting.
I was using literal integers instead of variables before. In previous versions of SQL Server, that used to be the best but doesn't appear to be so any more. Here's the test I just ran... (to be sure, it relies on a "right" value of no more than 7 digits, which we're still waiting to hear about.
Jeff Moden wrote:Jonathan AC Roberts wrote:with cte as
(
SELECT CONVERT(bigint, 111100000001234566) x
)
select x, convert(bigint,left(x,4) + right(x, 7))
from cteThat's works only if the right part is always 7 and I suspect it will not. It also has string conversions, which are typically slower than pure math. Without being guaranteed how long the right side will be, here's a pure math solution to figure out how long it is without string conversions but I suspect the use of LOG and FLOOR will make it a bit slower. I haven't tested with method for performance.
DECLARE @Test BIGINT = 111100000001234566
,@Parse BIGINT = 100000000000000
;
--===== Parsing the data as separate BIGINTs,
-- which is what I recommend for saving.
SELECT First4Digits = @Test/@Parse
,EveryThingElse = @Test%@Parse
;
--===== I haven''t tested for performance (see notes above) but here''s a pure
-- math solution if the significant length of the right side is not known.
SELECT @Test/@Parse*POWER(10,FLOOR(LOG(@Test%@Parse)/LOG(10)+1))+@Test%@Parse
;I still think it's a mistake to recombine the values in any fashion
Yes, I hadn't noticed that it was selecting the last 9 digits and removing the leading zeros from it. So it doesn't seem right to concatenate the values. But I would have thought that simple string operations should be faster than complex math operations like POWER.
I took my previous post down because that's correct. I was using POWER to figure out the significant digit length of the right side.
--Jeff Moden
June 7, 2022 at 1:40 am
Until we know for sure that the left side is absolutely guaranteed to be 4 digits with no leading zeros and the same with the right side with no leading zeros, it's probably not a good idea to to it mathematically even with the pure integer math does doesn't require the use of POWER. The test data generator has those as variable so you can see why I say that.
Interestingly enough, I also found out quite by mistake that the use of variables to hold constants are actually faster than using numeric constants in the code. That's a change from what I used to observe in older code. It could also be because numeric constants, even if they look like they should be integers, are converted to NUMERIC(9) or NUMERIC(13) instead of BIGINT (much to my surprise).
This has been a fun post. Thanks Christi and Jonathan.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply