Post reply

Help with arithmetic formula to "stuff" digits

  • April 6, 2015 at 12:59 pm

    #302817

    I'm not sure if this can be done. I'm not sure if it's the lack of coffee in my body or something else, but I'd like to know if someone has a better idea.

    I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.

    I already have 2 solutions using strings, but I'd like to know if there's a better option.

    SELECT Cg.Fiid,

    --Original Formula

    SUBSTRING( CONVERT( varchar, Cg.Fiid ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, Cg.Fiid ), 5, 10 ), 5 ),

    --My solution

    STUFF(Cg.Fiid, 5, 0, REPLICATE('0', 9 - LEN(Cg.Fiid)))

    FROM (VALUES(71927),

    (498932),

    (498934),

    (38061278))Cg(Fiid)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 6, 2015 at 1:46 pm

    #1789049

    Luis Cazares (4/6/2015)

    I'm not sure if this can be done. I'm not sure if it's the lack of coffee in my body or something else, but I'd like to know if someone has a better idea.

    Had my portion of industrial strength Espresso for the day, looking at my suggestion, maybe I should cut down on the coffee:rolleyes:, this was the simplest thing I came up with.

    😎

    SELECT Cg.Fiid,

    --Original Formula

    SUBSTRING( CONVERT( varchar, Cg.Fiid ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, Cg.Fiid ), 5, 10 ), 5 ),

    --My solution

    STUFF(Cg.Fiid, 5, 0, REPLICATE('0', 9 - LEN(Cg.Fiid))),

    -- EE arithmetic suggestion, not that it's any better ;-p

    (((Cg.Fiid * POWER(10,(9 - CEILING(LOG10(Cg.Fiid))))) / 100000) * 100000) + (Cg.Fiid % POWER(10,5 - (9 - CEILING(LOG10(Cg.Fiid)))))

    FROM (VALUES(71927),

    (498932),

    (498934),

    (38061278))Cg(Fiid);

  • April 6, 2015 at 1:54 pm

    #1789055

    This would be my only suggestion for an alternative. Don't think it's any better at all, other than that it can handle an input value this is only 4 digits long:

    SELECT Cg.Fiid,

    LEFT(Cg.Fiid, 4) * 100000 + SUBSTRING(CAST(Cg.Fiid AS varchar(9)), 5, 5)

    FROM (VALUES(71927),

    (498932),

    (4988) --<--,

    (498934),

    (0498934),

    (04989340),

    (38061278))Cg(Fiid)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • April 7, 2015 at 8:46 am

    #1789212

    Thank you both for your solutions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 7, 2015 at 8:53 am

    #1789216

    Luis Cazares (4/7/2015)

    Thank you both for your solutions.

    I would be careful using my version if the initial number of digits goes below 5 but hey! you can figure that one out, right?:-D

    😎

  • April 7, 2015 at 9:52 am

    #1789245

    If there are less than 5 digits, it means that there are bigger problems than a formula.

    However, I'll stay with the original because I was trying to improve the process but I don't have enough time left to test correctly.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 7, 2015 at 1:22 pm

    #1789316

    Luis Cazares (4/7/2015)

    If there are less than 5 digits, it means that there are bigger problems than a formula.

    However, I'll stay with the original because I was trying to improve the process but I don't have enough time left to test correctly.

    Quick testing thingy

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @SAMPLE_SIZE INT = 1000000;

    ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    , NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 1234 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    SELECT

    NM.N

    ,SUBSTRING( CONVERT( varchar, NM.N ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, NM.N ), 5, 10 ), 5 ) as FirstX

    -- --My solution

    ,STUFF(NM.N, 5, 0, REPLICATE('0', 9 - LEN(NM.N))) as secondx

    -- EE arithmetic suggestion, not that it's any better ;-p

    ,(((NM.N * POWER(10,(9 - FLOOR(LOG10(NM.N))))) / 100000) * 100000) + ((NM.N % (POWER(10,8 - (9 - FLOOR(LOG10(NM.N * 10)))))) % 100) as thirdX

    FROM NUMS NM;

  • April 7, 2015 at 1:28 pm

    #1789318

    It's easy to test this formula, but that's just the tip of the iceberg on this process. 🙂

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 7, 2015 at 1:33 pm

    #1789319

    Luis Cazares (4/7/2015)

    It's easy to test this formula, but that's just the tip of the iceberg on this process. 🙂

    I know all about Icebergs 😀

    😎

  • April 7, 2015 at 7:45 pm

    #1789393

    Luis! So rare to see you posting a question I had to try to help out.

    SELECT Cg.Fiid,

    --Original Formula

    SUBSTRING( CONVERT( varchar, Cg.Fiid ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, Cg.Fiid ), 5, 10 ), 5 ),

    --My solution

    STUFF(Cg.Fiid, 5, 0, REPLICATE('0', 9 - LEN(Cg.Fiid)))

    -- Dwain.C's all numeric solution

    ,Fiid/POWER(10,CAST(LOG10(Fiid) AS INT)-3)*100000+Fiid%POWER(10,CAST(LOG10(Fiid) AS INT)-3)

    FROM (VALUES(71927),

    (498932),

    (498934),

    (38061278))Cg(Fiid)

    Being an all numeric solution it might not be too bad.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • April 8, 2015 at 12:26 pm

    #1789659

    CELKO (4/8/2015)

    I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than

    Why are using SQL like 1960's COBOL?That language used string functions and computations. SQL is a data language, so strings have regular expressions that are declarative to prevent bad data. Since SQL is a tiered architecture, the input layers should do what you want to do the procedural code.

    foobar CHAR(9) NOT NULL

    CHECK (foobar LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

    Because I'm forced to do it.

    You could have quoted the following sentence which indicates that. Even your suggestion is wrong because instead of 1 column, I would need 2 as it refers to 2 different attributes.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 8, 2015 at 12:27 pm

    #1789662

    Eirikur Eiriksson (4/6/2015)

    Luis Cazares (4/6/2015)

    I'm not sure if this can be done. I'm not sure if it's the lack of coffee in my body or something else, but I'd like to know if someone has a better idea.

    Had my portion of industrial strength Espresso for the day, looking at my suggestion, maybe I should cut down on the coffee:rolleyes:, this was the simplest thing I came up with.

    😎

    SELECT Cg.Fiid,

    --Original Formula

    SUBSTRING( CONVERT( varchar, Cg.Fiid ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, Cg.Fiid ), 5, 10 ), 5 ),

    --My solution

    STUFF(Cg.Fiid, 5, 0, REPLICATE('0', 9 - LEN(Cg.Fiid))),

    -- EE arithmetic suggestion, not that it's any better ;-p

    (((Cg.Fiid * POWER(10,(9 - CEILING(LOG10(Cg.Fiid))))) / 100000) * 100000) + (Cg.Fiid % POWER(10,5 - (9 - CEILING(LOG10(Cg.Fiid)))))

    FROM (VALUES(71927),

    (498932),

    (498934),

    (38061278))Cg(Fiid);

    Ouch, what a mess, just came back to look at the code and realized that the LOG10 must be FLOOR'ed not CEILING'ed, this will throw off any number that is divisible by 10^LOG10(NUM). Must get stronger coffee:w00t:

    😎

  • April 8, 2015 at 1:03 pm

    #1789678

    Thank you all for your help.

    After a quick speed test, I got the following execution times after 10 executions for one million rows.

    -----------------------------------------------------------------------------------------

    |Version | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | AVG |

    |---------|------|------|------|------|------|------|------|------|------|------|-------|

    |Original | 1032 | 1047 | 984 | 1032 | 1015 | 1000 | 1031 | 1078 | 1015 | 1078 | 1031.2|

    |Luis | 828 | 844 | 828 | 828 | 828 | 859 | 828 | 859 | 844 | 860 | 840.6|

    |Eirikur | 1640 | 1656 | 1609 | 1641 | 1625 | 1656 | 1625 | 1672 | 1609 | 1687 | 1642.0|

    |Scott | 938 | 922 | 906 | 953 | 906 | 969 | 954 | 1047 | 906 | 953 | 945.4|

    |Dwain | 1328 | 1297 | 1297 | 1297 | 1312 | 1344 | 1313 | 1328 | 1328 | 1344 | 1318.8|

    -----------------------------------------------------------------------------------------

    It seems that all numeric solutions won't be the best option in here because of the complexity of the problem.

    PS. This wasn't meant to brag about my solution, but hopefully it applies the principle of "Make it Work, Make it Fast, Make it Pretty" 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 8, 2015 at 5:35 pm

    #1789740

    Luis Cazares (4/8/2015)

    Thank you all for your help.

    After a quick speed test, I got the following execution times after 10 executions for one million rows.

    -----------------------------------------------------------------------------------------

    |Version | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | AVG |

    |---------|------|------|------|------|------|------|------|------|------|------|-------|

    |Original | 1032 | 1047 | 984 | 1032 | 1015 | 1000 | 1031 | 1078 | 1015 | 1078 | 1031.2|

    |Luis | 828 | 844 | 828 | 828 | 828 | 859 | 828 | 859 | 844 | 860 | 840.6|

    |Eirikur | 1640 | 1656 | 1609 | 1641 | 1625 | 1656 | 1625 | 1672 | 1609 | 1687 | 1642.0|

    |Scott | 938 | 922 | 906 | 953 | 906 | 969 | 954 | 1047 | 906 | 953 | 945.4|

    |Dwain | 1328 | 1297 | 1297 | 1297 | 1312 | 1344 | 1313 | 1328 | 1328 | 1344 | 1318.8|

    -----------------------------------------------------------------------------------------

    It seems that all numeric solutions won't be the best option in here because of the complexity of the problem.

    PS. This wasn't meant to brag about my solution, but hopefully it applies the principle of "Make it Work, Make it Fast, Make it Pretty" 😀

    Fortunately I wasn't trying for fast or pretty, just having a little fun!

    Make it Work, Make it Fast, Make it Pretty[/url]

    But I suppose I should live by my own words (well, Jeff Moden's really).

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • April 9, 2015 at 9:46 am

    #1789909

    CELKO (4/8/2015)

    I have a column which needs to be 9 digits long.

    I gave you a simple LIKE predicate to assure this, as per your request. I thought I did pretty good without any DDL that is considered minimal Netiquette.

    The first four digits are independent from the other digits.

    Are you trying to say that head of an encoding modeled with this string has nothing to do with the tail? Like the shoe size and IQ have been crammed into one column?

    You could have an account number and a dependent check sequence in a valid design, but you do not. Yes, this is an awful design! In fact, I think it so awful that this not not the case.

    The following digits {tail of string} need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.

    What is the truth?

    1) Your nine digits encode the single attribute. It is possible for a single attribute to have multiple fields {note the use of field in RDBMS! a non-atomic part of an encoding}.

    2) Four digits encode one attribute and five digits encode a second attribute. They may or may not have a relationship that is enforced by constraints.

    All you have gotten is some COBOL-style string handling kludges and not a real fix to the bad design.

    Not everyone is as awful at COBOL as you apparently were. Some of us wrote excellent COBOL code as well. COBOL is not inherently evil, it's a language like any other: it has its place for its uses.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

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