Fix The WTF

  • ColdPie 2005-09-06 14:04
    I know absolutely nothing about T-SQL, but if I had to guess, would this work?<br>
    <br>
    @sequenceCode = @sequenceCode + 1<br>
    <br>
    Do I win?<br>
  • Ytram 2005-09-06 14:09
    All right, I've done a little bit of T-SQL, but just enough to do fairly standard CRUD stored procedures, but here goes:<br>
    <br>
    DECLARE @Result int<br>
    <br>
    SET @Result = CAST(@sequenceCode as int) + 1<br>
    <br>
    RETURN @Result<br>
  • Jake Heidt 2005-09-06 14:11
    You missed the fringe case of 99 = 00 :P<br>
    <br>
    I think it would go along the lines of.<br>
    <br>
    Declare @newval int<br>
    Declare @parsedVal int<br>
    Declare @out varchar<br>
    Set @parsedVal = Cast(@sequenceCode as int)<br>
    Set @newval = @parsedVal + 1<br>
    Set @out = Cast(@parsedVal as varchar) where @parsedVal >= 10<br>
    Set @out = '0' + Cast(@parsedVal as varchar) where @parsedVal < 10<br>
    Set @out = '00' where @parsedVal = 100<br>
    <br>
    TSQL is so ugly.<br>
    <br>
    Obviously theres just a plain better way to do this.<br>
  • daralick 2005-09-06 14:12
    set @sequenceCode = cast(@sequenceCode as int) + 1
    if len(@sequenceCode) < 2
    Set @sequenceCode = '0' + @sequenceCode

    if len(@sequenceCode) > 2
    Set @sequenceCode = '00'

    daralick
  • Striker 2005-09-06 14:14
    <P>In order to prove that I do too much in T-SQL, how about&nbsp;this?:</P>
    <P>set @sequenceCode = right( '0' + cast( cast( @sequenceCode as int) + 1 as varchar(3) ), 2)</P>
  • Maurits 2005-09-06 14:14
    Prepwork..<br>
    <br>
    CREATE TABLE sequencecodemap<br>
    (<br>
    &nbsp; &nbsp; this char(2),<br>
    &nbsp;&nbsp;&nbsp; next char(2)<br>
    )<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>
    VALUES ('00', '01')<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('01', '02')<br>
    <br>
    ...<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('99', '00')<br>
    <br>
    Finally...<br>
    <br>
    SELECT<br>
    &nbsp;&nbsp;&nbsp; @SequenceCode = Next<br>
    FROM<br>
    &nbsp;&nbsp;&nbsp; SequenceCodeMap<br>
    WHERE<br>
    &nbsp;&nbsp;&nbsp; @SequenceCode = This<br>
  • daralick 2005-09-06 14:14
    <br>
    set @sequenceCode = cast(@sequenceCode as int) + 1
    <br>
    if len(@sequenceCode) < 2
    <br>
    Set @sequenceCode = '0' + @sequenceCode
    <br>
    if len(@sequenceCode) > 2
    <br>
    Set @sequenceCode = '00'

    correction.. html hmm must need \<br\>

    daralick

  • Mung Kee 2005-09-06 14:18
    I cannot overstate how truly amazed I am at the number of big stupid
    switch statements or if...else statements we see here.&nbsp; Once the
    number of comparisons exceeds a handful, it may be time to start
    thinking about a more pragmatic solution.&nbsp; I think a lot of
    developers lack this process of graduated thought.<br>
  • Anonymous 2005-09-06 14:23
    I hate to admit it, but it took at least two minutes, with testing, for me to get it right:<br>
    &nbsp;<br>
    <><>IF @sequenceCode like '[0-9][0-9]'<span style="font-family: monospace;"><br>
    </span>&nbsp; set @sequenceCode = right(convert(char(3), convert(int, @sequenceCode) + 101), 2)</>&nbsp;</><br>
  • travisowens 2005-09-06 14:23
    <P>Ok let's dive into the original coder's mind and assume that there's no way to casting or type conversion in T-SQL.</P>
    <P>This code is the worst of all the repititve code I've seen on here in a long time, this guy really hand typed 100 conversions?!</P>
    <P>Would it have been better to reverse the string and add 1 to the first digit by doing his conversion, but only typed 0-9, and if we're at 9 and adding one, add one to the next digit, and so forth.&nbsp; While it would have required *gasp* a loop, at least he could have done his conversion in 10 checks instead of 100.</P>
    <P>So what happens when his database needs to support more than 0-100, what about 100,000?&nbsp; That's a whole month of copying and pasting.</P>
  • JRSTEELE 2005-09-06 14:23
    SET&nbsp;@sequenceCode = LPAD(&nbsp;CONVERT( Char(2), (CAST(@sequenceCode as int) + 1)&nbsp;% 100), 2, '0')
  • retnuh 2005-09-06 14:24
    declare @test char(2)<br>
    <br>
    set @test = '12'<br>
    <br>
    set @test = @test + 1<br>
    <br>
    select @test
  • res2 2005-09-06 14:26
    <P>
    Anonymous:
    You missed the fringe case of 99 = 00 :P<BR><BR>I think it would go along the lines of.<BR><BR>Declare @newval int<BR>Declare @parsedVal int<BR>Declare @out varchar<BR>Set @parsedVal = Cast(@sequenceCode as int)<BR>Set @newval = @parsedVal + 1<BR>Set @out = Cast(@parsedVal as varchar) where @parsedVal &gt;= 10<BR>Set @out = '0' + Cast(@parsedVal as varchar) where @parsedVal &lt; 10<BR>Set @out = '00' where @parsedVal = 100<BR><BR>TSQL is so ugly.<BR><BR>Obviously theres just a plain better way to do this.<BR>
    </P>
    <P>There is a second special case, if the code is 00 then it stays 00...</P>
  • kipthegreat 2005-09-06 14:29
    travisowens:
    <p>So what happens when his database needs to
    support more than 0-100, what about 100,000?&nbsp; That's a whole month
    of copying and pasting.</p>
    <br>
    <br>
    That's what interns are for!<br>
  • Anonymous 2005-09-06 14:30
    res2:
    <p>There is a second special case, if the code is 00 then it stays 00...</p>
    <br>
    <br>
    Dang!&nbsp; Missed that.&nbsp; I did catch that it remains unchanged if it isn't two numeric digits.<br>
    <br>
    Heres the modified code:<br>
    &nbsp;<br>
    IF @sequenceCode like '[0-9][0-9]' and @sequenceCode &lt;&gt; '00'<br>
    &nbsp; set @sequenceCode = right(convert(char(3), convert(int, @sequenceCode) + 101), 2)<br>
    <br>
  • Richard Nixon 2005-09-06 14:35
    Maurits:
    Prepwork..<br>
    <br>
    CREATE TABLE sequencecodemap<br>
    (<br>
    &nbsp; &nbsp; this char(2),<br>
    &nbsp;&nbsp;&nbsp; next char(2)<br>
    )<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>
    VALUES ('00', '01')<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('01', '02')<br>
    <br>
    ...<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('99', '00')<br>
    <br>
    Finally...<br>
    <br>
    SELECT<br>
    &nbsp;&nbsp;&nbsp; @SequenceCode = Next<br>
    FROM<br>
    &nbsp;&nbsp;&nbsp; SequenceCodeMap<br>
    WHERE<br>
    &nbsp;&nbsp;&nbsp; @SequenceCode = This<br>
    <br>
    <br>
    Good show! <br>
  • daralick 2005-09-06 14:35
    set @sequenceCode = right( ('0' + cast( (cast(@sequenceCode as int) + 1) as varchar(3))) , 2)<br>
    <br>
    hmm, must remember review before posting...<br>
  • Gene Wirchenko 2005-09-06 14:39
    And when the sequence code is "00" (as after the "99" case is incremented)?<br>
    <br>
    Sincerely,<br>
    <br>
    Gene Wirchenko<br>
    <br>
  • Paul Tomblin 2005-09-06 14:43
    Maurits:
    Prepwork..<br>
    <br>
    CREATE TABLE sequencecodemap<br>
    (<br>
    &nbsp; &nbsp; this char(2),<br>
    &nbsp;&nbsp;&nbsp; next char(2)<br>
    )<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>
    VALUES ('00', '01')<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('01', '02')<br>
    <br>
    ...<br>
    snip<br>
    <br>
    <br>
    You're just trying to get into next week's WTF, right?<br>
    <br>
  • Ross Presser 2005-09-06 14:44
    set @sequenceCode = right( cast ( 101 + cast( @sequenceCode as int) as char(3)) , 2)<br>
    <br>
    elapsed time: 30 sec (including testing)<br>
  • JRSTEELE 2005-09-06 14:45
    <P>IF (CAST(@sequenceCode as int) &gt; 0) BEGIN</P>
    <P>&nbsp;&nbsp;&nbsp;SET&nbsp;@sequenceCode = LPAD(&nbsp;CONVERT( Char(2), (CAST(@sequenceCode as int) + 1)&nbsp;% 100), 2, '0') </P>
    <P>END</P>
  • Wire 2005-09-06 14:45
    <b>JRSTEELE</b> - T-SQL does not support lpad

    The following replicates the <i>'00' does not increment</i> of the original:

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF (@sequenceCode BETWEEN '01' AND '99') BEGIN
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET @sequenceCode = right(cast(@sequenceCode + 1 as varchar), 2)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END ELSE BEGIN
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET @sequenceCode = '00'
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END
  • Wire 2005-09-06 14:45
    NT
  • Anonymous 2005-09-06 14:48
    Wire:
    <b></b><br>
    The following replicates the <i>'00' does not increment</i> of the original:<br>
    <br>
    <br>
    Now test both with a value of 'XX'.<br>
    <br>
  • David 2005-09-06 14:49
    <P>
    Maurits:
    Prepwork..<BR><BR>CREATE TABLE sequencecodemap<BR>(<BR>&nbsp; &nbsp; this char(2),<BR>&nbsp;&nbsp;&nbsp; next char(2)<BR>)<BR><BR>INSERT INTO sequencecodemap (this, next)<BR>VALUES ('00', '01')<BR><BR>INSERT INTO sequencecodemap (this, next)<BR>VALUES ('01', '02')<BR><BR>...<BR><BR>INSERT INTO sequencecodemap (this, next)<BR>VALUES ('99', '00')<BR><BR>Finally...<BR><BR>SELECT<BR>&nbsp;&nbsp;&nbsp; @SequenceCode = Next<BR>FROM<BR>&nbsp;&nbsp;&nbsp; SequenceCodeMap<BR>WHERE<BR>&nbsp;&nbsp;&nbsp; @SequenceCode = This<BR>
    </P>
    <P>Best.&nbsp; Solution.&nbsp; Evar.&nbsp; Truly leveraging the full dynamic range of your core competencies as well as&nbsp;the available technology.</P>
    <P>&nbsp;</P>
    <P>On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.&nbsp; Not even business speak, but real actual person to person colloquial conversation.&nbsp; Apparently they're too good for the word 'use.'</P>
  • Ytram 2005-09-06 14:53
    <span id="_ctl0_PostForm_Reply">On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.
    <br>
    <br>
    You mean leverage the word 'leverage', right?<br>
    </span>
  • JRSTEELE 2005-09-06 14:54
    <P>No LPAD??? OK...</P>
    <P>IF (CAST(@sequenceCode as int) &gt; 0) BEGIN</P>
    <P>&nbsp;&nbsp;&nbsp;SET @sequenceCode = RIGHT(&nbsp;'0' + CONVERT( Char(2), (CAST(@sequenceCode as int) + 1)&nbsp;% 100), 2) </P>
    <P>END</P>
    <P>&nbsp;</P>
    <P>This code</P>
    <P>a) does not increment the value if @sequenceCode = '00' or is equla to anyother nonnumeric value (like the original).</P>
    <P>b) pads the left with a 0 is the number is less than 10 (like the original)</P>
    <P>c) returns the value in the original variable @sequenceCode (like the original).</P>
  • Wire 2005-09-06 14:55
    ...and in checking for an lpad function I dropped the leading '0'.

    SET @sequenceCode = right('0' + cast(@sequenceCode + 1 as varchar), 2)
  • Volmarias 2005-09-06 15:02
    travisowens:
    <P>This code is the worst of all the repititve code I've seen on here in a long time, this guy really hand typed 100 conversions?!</P>


    No, of course he didn't! He wrote a program to give him those cases! For your viewing pleasure, I give you: WTFGenerator, in Java!

    public class WTFGenerator {
    public static void main(String[] args) {
    for(int i = 1; i!=100; ;) {
    System.out.println("IF @sequenceCode = '" + prettyMethod(x++) + "'");
    System.out.println("SET @sequenceCode = '" + prettyMethod(x) + "'");
    }
    }

    public String prettyMethod (int x) {
    if(x < 10) return "0" + x;
    return new String(x);
    }
    }

    Copy + Paste and there you go! Now, I haven't actually tried to compile this, so it probably doesn't work, but I think that I've solved today's WTF!
  • JRSTEELE 2005-09-06 15:03
    Anonymous:

    <P>No LPAD??? OK...</P>
    <P>IF (CAST(@sequenceCode as int) &gt; 0) BEGIN</P>
    <P>&nbsp;&nbsp;&nbsp;SET @sequenceCode = RIGHT(&nbsp;'0' + CONVERT( Char(2), (CAST(@sequenceCode as int) + 1)&nbsp;% 100), 2) </P>
    <P>END</P>
    <P>&nbsp;</P>
    <P>This code</P>
    <P>a) does not increment the value if @sequenceCode = '00' or is equla to anyother nonnumeric value (like the original).</P>
    <P>b) pads the left with a 0 is the number is less than 10 (like the original)</P>
    <P>c) returns the value in the original variable @sequenceCode (like the original).</P>
    <P>
    </P>
    <P>&nbsp;</P>
    <P>I give up... Wire wins!&nbsp; I've been told my condition will die, and then I noticed the Modulo 100 isn't neccesary.</P>
  • DV 2005-09-06 15:03
    <P>Hmm. Why not make it a local temporary table inside of a stored procedure so that it gets created on every call.&nbsp; Don't want to mess up a pristine (I'm assuming) data model with a look up table.</P>
  • Volmarias 2005-09-06 15:03
    Pretend that the forums kept the formatting so that it looked presentable.

    Also, pretend that the second println statement starts with \t :)
  • Mike R 2005-09-06 15:04
    <P>set @sequenceCode =&nbsp; right('0'+convert(varchar,(convert(int,@sequenceCode ) + 1)),2);</P>
    <P>[H]<BR></P>
  • Wire 2005-09-06 15:06
    This doesn't pad properly - the Char(2) includes a trailing space on single digit numbers, so '01' returns '2 '

  • Mike R 2005-09-06 15:06
    Mike R:

    <P>set @sequenceCode =&nbsp; right('0'+convert(varchar,(convert(int,@sequenceCode ) + 1)),2);</P>
    <P>[H]<BR></P>
    <P>
    </P>
    <P>Ahhcrap! someone did it already.</P>
  • Hugo Kornelis 2005-09-06 15:07
    Anonymous:

    <P>This code</P>
    <P>
    </P>
    <P>d) Results in an error: "Incorrect syntax near ')'", because there is no END to match the BEGIN</P>
    <P>e) (After removing unneeded BEGIN) Yields run-time error if @sequenceCode is not numeric: "Syntax error converting the varchar value 'XX' to a column of data type int."</P>
    <P>Best, Hugo</P>
  • Wire 2005-09-06 15:07
    I can't believe how much energy I've spent on this (not much, but still).
    Maybe I should get back to work.
  • Brent Ashley 2005-09-06 15:09
    this took me about 2 minutes and is quite similar:<br>
    <br>
    set @sequenceCode = right(convert(char(3),convert(int,'1'+@sequenceCode)+1),2)<br>
    <br>
    <br>
  • joost 2005-09-06 15:10
    Alex Papadimoulis:
    [...] and is best enjoyed if you've
    never programmed anything in T-SQL in your life before. [...]<br>
    <br>

    <p></p><pre>SET @number = INT(@sequenceCode)<br>IF @number &gt; 0<br> SET @number = @number + 1<br> IF @number = 100 <br> SET @number = 0<br> IF @number &lt; 10<br> &nbsp;SET @sequenceCode = '0' + STR(@number)<br> ELSE<br> SET @sequenceCode = STR(@number)<br>DROP DATABASE<br><br></pre>
  • David 2005-09-06 15:14
    <P>
    Ytram:
    <SPAN id=_ctl0_PostForm_Reply>On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.
    <BR><BR>You mean leverage the word 'leverage', right?<BR></SPAN>
    </P>
    <P>[+o(]....bastard...</P>
    <P>&nbsp;</P>
    <P>[:P]</P>
  • endo 2005-09-06 15:22
    joost:
    Alex Papadimoulis:
    [...] and is best enjoyed if you've never programmed anything in T-SQL in your life before. [...]<BR>
    <BR>
    <P></P><PRE>SET @number = INT(@sequenceCode)<BR>IF @number &gt; 0<BR> SET @number = @number + 1<BR> IF @number = 100 <BR> SET @number = 0<BR> IF @number &lt; 10<BR> &nbsp;SET @sequenceCode = '0' + STR(@number)<BR> ELSE<BR> SET @sequenceCode = STR(@number)<BR><STRONG>DROP DATABASE</STRONG><BR><BR></PRE>
    <P>
    </P>
    <P>Nice, no one will miss that database anyway :) </P>
  • JRG 2005-09-06 15:23
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
  • Anonymous 2005-09-06 15:26
    JRG:
    Slightly shorter, uses implicit casting...<br><br><code>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </code>
    <br>
    <br>
    Were we told what the database product was?<br>
    <br>
    com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from
    datatype 'VARCHAR' to 'INT' is not allowed.&nbsp; Use the CONVERT
    function to run this query.<br>
    Error code: 257<br>
    SQL state: 42000<br>
    <br>
  • JRG 2005-09-06 15:30
    Sorry, everyone's talking about T-SQL though...
  • JRG 2005-09-06 15:31
    <P>
    Anonymous:
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    <BR><BR>Were we told what the database product was?<BR><BR>com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.&nbsp; Use the CONVERT function to run this query.<BR>Error code: 257<BR>SQL state: 42000<BR><BR>
    </P>
    <P>Actually, it's there in the OPs message... T-SQL.</P>
  • msumerano 2005-09-06 15:31
    <P>Handles anything in the SequenceNumber and properly "wraps" around when the passed in sequence number is 99.</P><CODE><PRE>declare @SequenceNumber char(2)
    select @SequenceNumber = '95'

    declare @sn int

    if (isnumeric(@SequenceNumber) = 1)
    select @sn = convert(int, @SequenceNumber)
    else
    select @sn = 0

    if (@sn &lt; 0 or @sn &gt;= 99)
    select @SequenceNumber = '00'
    else
    select @SequenceNumber = right('0' + convert(varchar, (@sn + 1)), 2)

    select @SequenceNumber
    </PRE></CODE>
  • David Ostroske 2005-09-06 15:42
    <P>Not the most elegant solution. But it works. And it won't barf when not-numeric characters are passed in.</P><PRE>SET @sequenceCode = CASE<BR>&nbsp; WHEN @sequenceCode = '00'<BR>&nbsp;&nbsp;&nbsp; THEN '00'<BR>&nbsp; WHEN @sequenceCode LIKE '[0-9][0-9]'<BR>&nbsp;&nbsp;&nbsp; THEN RIGHT('00' + CAST(CAST(@sequenceCode AS INTEGER)+1 AS VARCHAR(3)), 2)<BR>&nbsp;&nbsp;&nbsp; ELSE @sequenceCode<BR>END</PRE>
  • Anonymous 2005-09-06 15:51
    JRG:
    <p>
    Anonymous:
    JRG:
    Slightly shorter, uses implicit casting...<br><br><code>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </code>
    <br><br>Were we told what the database product was?<br><br>com.sybase.jdbc2.jdbc.SybSQLException:
    Implicit conversion from datatype 'VARCHAR' to 'INT' is not
    allowed.&nbsp; Use the CONVERT function to run this query.<br>Error code: 257<br>SQL state: 42000<br><br>
    </p>
    <p>Actually, it's there in the OPs message... T-SQL.</p>
    <br>
    Sybase invented T-SQL.&nbsp; Microsoft, having acquired rights to use
    it, has modified it in their product.&nbsp; "T-SQL" by itself doesn't
    tell you if the implicit cast will work.<br>
    <br>
  • Bustaz Kool 2005-09-06 16:01
    <P>
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    </P>
    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
  • Bustaz Kool 2005-09-06 16:02
    Bustaz Kool:

    <P>
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    </P>
    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
    <P>
    </P>
    <P>And most of the multiple liners, too.</P>
  • -L 2005-09-06 16:07
    Nah, you can do this neatly as well: First construct a single-digit BCD
    adder with carry. Then chain two of these together. If the MSD adder
    carries, set both digits to zero. Trivial.<br>
  • Anonymous 2005-09-06 16:15
    Bustaz Kool:
    <p>
    JRG:
    Slightly shorter, uses implicit casting...<br><br><code>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </code>
    </p>
    <p>As far as I can discern, all of the one liners throw an error on invalid input.</p>
    <br>
    <br>
    Not knowing the application or the database, I can't say what is
    invalid input.  Perhaps (by design) values other than two digits
    have special meaning and must be preserved.  If we assume we
    should preserve current behavior, suggested replacement code should be
    tested with strings like '2', '-6', '3D', 'NA', etc. to ensure that the
    original value is preserved.<br>
    <br>
  • Oliver Klozoff 2005-09-06 16:17

    SET @sequenceCode = CASE

    WHEN (@sequenceCode LIKE '[0-9][1-9]')
    or (@sequenceCode LIKE '[1-9][0-9]')

    THEN replace(replace(str(@sequenceCode+1, 2),' ','0'),'*','0')

    ELSE @sequenceCode

    END



    Everyone knows that the costliest part of this is the conditionals, and I only have one!
  • joost 2005-09-06 16:36
    msumerano:
    <p>Handles anything in the SequenceNumber and properly "wraps" around when the passed in sequence number is 99.</p><code></code><pre>declare @SequenceNumber char(2)<br>select @SequenceNumber = '95'<br><br>declare @sn int<br><br>if (isnumeric(@SequenceNumber) = 1)<br> select @sn = convert(int, @SequenceNumber)<br>else<br> select @sn = 0<br><br>if (@sn &lt; 0 or @sn &gt;= 99)<br> select @SequenceNumber = '00'<br>else<br> select @SequenceNumber = right('0' + convert(varchar, (@sn + 1)), 2)<br><br>select @SequenceNumber<br></pre>
    <br>
    <br>
    Way to handle boolean function results! Only, the second test should be:<br>

    <br>

    <span style="font-family: courier new;"># I only trust the = operator</span><br style="font-family: courier new;">

    <span style="font-family: courier new;">if (($sn &lt; 0) = 1 or ($sn &gt;= 99) = 1)</span><br style="font-family: courier new;">

    <br>
    And maybe the first should be...<br>
    <br>
    <span style="font-family: courier new;"></span><span style="font-family: courier new;">if (((isnumeric(@sequenceNumber) = 1) &gt; 0) = 1)</span><br>
    <br>
    Looks kinda cheery. (And (Ever (So (Lispy (.))))) But hey, we can
    always blame the T-SQL "programming" "language" for things like this ;-)<br>
    <br>
    Disclaimer: I don't know (how a Boolean is represented in) T-SQL<br>
  • tw000 2005-09-06 16:45
    Damn, if I didn't laugh out loud reading that. My brain managed to "censor" the "second special case."
  • Maurits 2005-09-06 16:45
    joost:
    I don't know (how a Boolean is represented in) T-SQL
    <br>
    <br>
    Either<br>
    CAST(1 AS BIT) -- true<br>
    CAST(0 AS BIT) -- false<br>
    <br>
    or<br>
    DECLARE @True bit, @False bit<br>
    SELECT @True = 1, @False = 0<br>
  • JRG 2005-09-06 17:05
    Bustaz Kool:

    <P>
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    </P>
    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
    <P>
    </P>
    <P>You do filter your input before you use it don't you?</P>
  • Joe 2005-09-06 17:08
    How did this get in here?
  • Matt S 2005-09-06 17:12
    That is exactly what I was thinking.<br>
  • OneFactor 2005-09-06 17:27
    Considering how many "improvements"&nbsp;fail to leave '00' and&nbsp;non-numeric inputs alone, I'd be tempted to not fix the WTF if I ran across the code. An elegant yet erroneous change does not count as "fixing" the WTF.
  • OneFactor 2005-09-06 17:35
    Bustaz Kool:

    <P>
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    </P>
    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
    <P>
    </P>
    <P>Ok let me try:</P>
    <P>set @sequenceCode = case when (CAST(@sequenceCode as int) &gt; 0) then right( cast&nbsp;101 + cast( @sequenceCode as int) as char(3)) , 2) else @sequenceCode end</P>
  • JRG 2005-09-06 17:44
    OneFactor:
    Bustaz Kool:

    <P>
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    </P>
    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
    <P>
    </P>
    <P>Ok let me try:</P>
    <P>set @sequenceCode = case when (CAST(@sequenceCode as int) &gt; 0) then right( cast&nbsp;101 + cast( @sequenceCode as int) as char(3)) , 2) else @sequenceCode end</P>
    <P>
    </P>
    <P>The cast will fail on non-numeric input.</P>
    <P>&nbsp;</P>
    <code>
    DECLARE @sequenceCode VARCHAR(2)
    SET @sequenceCode = '99'

    -- Do your input validation somewhere here...

    SET @sequenceCode =
    CASE
    WHEN ISNUMERIC(@sequenceCode) = 0 THEN @sequenceCode
    WHEN @sequenceCode = '00' THEN '00'
    ELSE RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)
    END

    SELECT @sequenceCode
    </code>
  • JRG 2005-09-06 17:45
    OneFactor:
    Bustaz Kool:

    <P>
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    </P>
    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
    <P>
    </P>
    <P>Ok let me try:</P>
    <P>set @sequenceCode = case when (CAST(@sequenceCode as int) &gt; 0) then right( cast&nbsp;101 + cast( @sequenceCode as int) as char(3)) , 2) else @sequenceCode end</P>
    <P>
    <BR><BR>(Stupid forum editor)</P>
    <P>The cast will fail on non-numeric input.</P>
    <P><CODE>DECLARE @sequenceCode VARCHAR(2) <BR>SET @sequenceCode = '99' <BR><BR>-- Do your input validation somewhere here... <BR><BR>SET @sequenceCode =&nbsp;<BR>&nbsp;&nbsp;&nbsp;CASE WHEN ISNUMERIC(@sequenceCode) = 0 THEN @sequenceCode&nbsp;<BR>&nbsp;&nbsp;&nbsp;WHEN @sequenceCode = '00' THEN '00'&nbsp;<BR>&nbsp;&nbsp;&nbsp;ELSE RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) <BR>END <BR><BR>SELECT @sequenceCode </CODE></P>
  • Fabian 2005-09-06 17:47
    Volmarias:
    <br>
    For your viewing pleasure, I give you: WTFGenerator, in Java!

    <br>
        public class WTFGenerator <br>
        {
    <br>
            public static void main(String[] args) <br>
            {
    <br>
                for(int i = 1; i!=100; ;) <br>
                {
    <br>
               
        System.out.println("IF @sequenceCode = '" +
    prettyMethod(x++) + "'");<br>
               
        System.out.println("SET @sequenceCode = '" +
    prettyMethod(x) + "'"); <br>
                 }<br>
            }

    <br>
    <br>
        public String prettyMethod (int x) <br>
        {
    <br>
            if(x < 10) return "0" + x;
    <br>
            return new String(x);
    <br>
        }
    <br>
    }

    <br>
    <br>
    Copy + Paste and there you go! Now, I haven't actually tried to compile
    this, so it probably doesn't work, but I think that I've solved today's
    WTF!
    <br>
    <br>
    Dude, as WTFGenerators go, this is a great one. The fact that you're
    incrementing x while looping over i is fairly obvious, but may lead to
    some severe crashes before someone finds out. All that's fairly
    trivial... But I especially love the "i != 100" (as opposed to the "i
    < 100") which is always garanteed to have the next person
    maintaining your code searching for at least a day before he finds the
    conditional  "i += 10".<br>
    <br>
    Brillant!<br>
    <br>
    Fabian<br>
  • Fabian 2005-09-06 17:52
    Has anyone here ever remarked on the quality of the forum software?<br>
    <br>
    Jus' kiddin'...<br>
    <br>
    What  was I was trying to say was:<br>
    "(as opposed to the "i &lt; 100") which is guaranteed to have 'the
    next guy' searching for at least a day before he finds the conditional
    "i += 10".<br>
    <br>
    (...praying this works...)<br>
  • Coward 2005-09-06 18:02
    In Ada:<br>
    inc num and watch out for that num > 99, ok?<br>
    <br>
  • md2perpe 2005-09-06 18:05
    If it had been MySQL, I would have set the column type to TINYINT(2) UNSIGNED ZEROFILL and used modulus (MOD 100) when incrementing it.<br>
  • Mushoo79 2005-09-06 18:47
    Couldn't you just do a Case statement and if case is equal to 99 set it to 0 otherwise add 1?<br>
    Codename: Mushoo<br>
  • Quinnum 2005-09-06 18:53
    Anonymous:
    JRG:

    <P>
    Anonymous:
    JRG:
    Slightly shorter, uses implicit casting...<BR><BR><CODE>SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) </CODE>
    <BR><BR>Were we told what the database product was?<BR><BR>com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.&nbsp; Use the CONVERT function to run this query.<BR>Error code: 257<BR>SQL state: 42000<BR><BR>
    </P>
    <P>Actually, it's there in the OPs message... T-SQL.</P>
    <P>
    <BR>Sybase invented T-SQL.&nbsp; Microsoft, having acquired rights to use it, has modified it in their product.&nbsp; "T-SQL" by itself doesn't tell you if the implicit cast will work.<BR><BR>
    </P>
    <P>Yeah, but using Sybase is a WTF in itself. It's like running a real old version of SQL Server.</P>
    <P>Sybase almost makes baby Jesus cry, although not as much as Oracle.</P>
    <P>&nbsp;</P>
  • Anon 2005-09-06 18:56
    It's a trick question, right? The WTF isn't (only) in this code. If a
    record might need to be incremented, then it shouldn't have been a
    decimal string in the first place. And if this string really does need
    to be incremented, then I'm not writing a line of code until I've
    gathered requirements and you've signed off on a definition of
    "increment" for char(2)s. Then I'll write the unit tests (I don't know
    how wide chars are here, if they're small enough these could be
    exhasutive), and your nominated technical reviewer can sign off on
    those. Then I'll write the code. But only if you're sure you don't need
    a design document.<br>
  • gary 2005-09-06 19:21
    travisowens:
    <p>Ok let's dive into the original coder's mind and assume that there's no way to casting or type conversion in T-SQL.</p>
    <p>This code is the worst of all the repititve code I've seen on here in a long time, this guy really hand typed 100 conversions?!</p>
    <p>Would it have been better to reverse the string and add 1 to the
    first digit by doing his conversion, but only typed 0-9, and if we're
    at 9 and adding one, add one to the next digit, and so forth. 
    While it would have required *gasp* a loop, at least he could have done
    his conversion in 10 checks instead of 100.</p>
    <p>So what happens when his database needs to support more than 0-100,
    what about 100,000?  That's a whole month of copying and pasting.</p>
    <br>
    <br>
    i wouldn't hand type all those conversions - i would write a perl
    script to generate the code for me, then copy and paste the result!!<br>
  • Your Name: 2005-09-06 19:26
    <P>What people doesn't seem to get is that he kept the numbers on the interval [00..99] because he wasn't going to cover the range [00..4294967295].</P>
    <P>Imagine if the column where this is inserted was an integer 64 bits!</P>
  • III 2005-09-06 20:36
    >If a record might need to be incremented, then it shouldn't have been a decimal string in the first place.

    i have a sneaking suspicion that this was a y2k fix, and that it eliminated mod100 year problems by storing the 2 digits as a string...
  • Foon 2005-09-06 20:39
    <P>set @sequenceCode = right(cast(cast( @sequenceCode as int) + 101 as varchar(3)), 2)</P>
    <P>or possibly even</P>
    <P>set @sequenceCode = cast(cast( @sequenceCode as int) + 101 as varchar(2))</P>
  • dance2die 2005-09-06 23:14
    All right.&nbsp; I have come to a conclusion that, some of you guys are
    having some problem understanding the problem of this topic.<br>
    <br>
    To ease the pain of reading If statements, i have come up with even more readable solution<br>
    <br>
    <br>
    SET @sequenceCode = (<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; CASE<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '01' THEN '02'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '02' THEN '03'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '03' THEN '04'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '04' THEN '05'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '05' THEN '06'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '06' THEN '07'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; -- snip --<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '94' THEN '95'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '95' THEN '96'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '96' THEN '97'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '97' THEN '98'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '98' THEN '99'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '99' THEN '00'<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; END<br>
    &nbsp;&nbsp;&nbsp; )<br>
    <br>
    <br>
  • diGriz 2005-09-07 00:15
    Senior programmer? Looks more like a senile programmer *shiver*<br>
  • vhawk 2005-09-07 01:07
    Hey Bill,<br>
    <br>
    Write me a stored proc that does this ..<br>
    <br>
    Now was that fast or what - 15 secs. :)<br>
  • ozark 2005-09-07 01:15
    All of you are trying to actually <span style="font-style: italic;">change</span>
    the algorithm, and use casting to an integer or something. Assume that
    casting is not available, or is very expensive (otherwise he would have
    used it, right?)<br>
    Besides, the goal is to "Fix the WTF", so it must still remain a WTF (albeit fixed) when done.<br>
    So, here's my solution. (I don't know the syntax for T-SQL arrays, so fix it further yourself.)<br>
    Array[100] = {'00','01','02','03', ... , '99'}<br>
    for(i=0;i<100;++i)<br>
     IF (@sequenceCode = Array[i])<br>
        {<br>
           SET @sequenceCode = Array[(i+1)%100];<br>
           break;<br>
        }<br>
    How's that?<br>
  • Dylan 2005-09-07 02:36
    -- Best to use a temporary table for this job. Much quicker.<br>
    Declare @i int<br>
    Declare @c char(2)<br>
    Declare @t table (GoFrom char(2), Go_To char(2))<br>
    <br>
    Set @i = 0<br>
    While (@i &lt; 100)<br>
    Begin<br>
    &nbsp;&nbsp; &nbsp;If @i = 0<br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Set @c = '00'<br>
    &nbsp;&nbsp; &nbsp;Else<br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Set @c = right('0' + cast(@i+1 as varchar(3)),2)<br>
    &nbsp;&nbsp; &nbsp;Insert Into @t (GoFrom, Go_To) values (right('0' + cast(@i as varchar(3)),2), @c)<br>
    &nbsp;&nbsp; &nbsp;Set @i=@i+1<br>
    End<br>
    Select @SequenceCode = isnull(Go_To,@SequenceCode) From @t Where GoFrom = @SequenceCode<br>
    <br>
  • Alpha Au 2005-09-07 02:37
    Maurits:
    Prepwork..<br>
    <br>
    CREATE TABLE sequencecodemap<br>
    (<br>
    &nbsp; &nbsp; this char(2),<br>
    &nbsp;&nbsp;&nbsp; next char(2)<br>
    )<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>
    VALUES ('00', '01')<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('01', '02')<br>
    <br>
    ...<br>
    <br>
    INSERT INTO sequencecodemap (this, next)<br>

    VALUES ('99', '00')<br>
    <br>
    Finally...<br>
    <br>
    SELECT<br>
    &nbsp;&nbsp;&nbsp; @SequenceCode = Next<br>
    FROM<br>
    &nbsp;&nbsp;&nbsp; SequenceCodeMap<br>
    WHERE<br>
    &nbsp;&nbsp;&nbsp; @SequenceCode = This<br>
    <br>
    <br>
    You Forgot to Build INDEX!! <br>
  • eddie 2005-09-07 02:59
    <P>
    dance2die:
    All right.&nbsp; I have come to a conclusion that, some of you guys are having some problem understanding the problem of this topic.<BR><BR>To ease the pain of reading If statements, i have come up with even more readable solution<BR><BR><BR>SET @sequenceCode = (<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; CASE<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '01' THEN '02'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '02' THEN '03'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '03' THEN '04'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '04' THEN '05'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '05' THEN '06'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '06' THEN '07'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; -- snip --<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '94' THEN '95'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '95' THEN '96'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '96' THEN '97'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '97' THEN '98'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '98' THEN '99'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN @sequenceCode = '99' THEN '00'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; END<BR>&nbsp;&nbsp;&nbsp; )<BR><BR><BR>
    </P>
    <P>That would be:</P>
    <P>SET @sequenceCode = (<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; CASE @sequenceCode&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN&nbsp;'01' THEN '02'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN '02' THEN '03'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN&nbsp;'03' THEN '04'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN&nbsp;'04' THEN '05'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN '05' THEN '06'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN '06' THEN '07'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; -- snip --<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN '94' THEN '95'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN&nbsp;'95' THEN '96'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN '96' THEN '97'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN&nbsp;'97' THEN '98'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN '98' THEN '99'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN&nbsp;'99' THEN '00'<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; END<BR>&nbsp;&nbsp;&nbsp; )<BR></P>
    <P>:)</P>
  • csrster 2005-09-07 03:05
    This is surely the correct answer (as md2perpe also pointed out). The WTF is using a <br>
    CHAR for a counter in the first place. <br>
    <br>
    So given this piece of arse-wipingly bad database design, what should one do?<br>
    a) refactor the database<br>
    b) do what the wtf guy did and write 100 conditionals<br>
    or<br>
    c) use some moderately intelligent programming with casting or a map to do it in<br>
    fewer lines.<br>
    <br>
    In real life I might well have ended up doing (b).<br>
  • dmitriy 2005-09-07 03:37
    The WTFGenerator class given by Volmarias does not compile:<br>
    <br>
    dmitriy@si[202]$ javac WTFGenerator.java <br>
    WTFGenerator.java:3: illegal start of expression<br>
    for(int i = 1; i!=100; ;) {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    ^<br>
    WTFGenerator.java:3: illegal start of expression<br>
    for(int i = 1; i!=100; ;) {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    ^<br>
    WTFGenerator.java:7: ';' expected<br>
    }<br>
    ^<br>
    3 errors<br>
    <br>
    Once I remove the errors in the code (those above and others), it
    prints out lines to set the value to 2 if it is 1, to 3 if it is 2, and
    so on. Similarly to some of the other submissions, it increments 99 to
    100.<br>
  • anonymous 2005-09-07 03:55
    <span id="PostFlatView"><pre><font>The obvious WTF is that he doesn't handle wrap around case properly. It should be:<br><br>ELSE</font> <font>IF</font> @sequenceCode = <font>'99'</font><br> <font>SET</font> @sequenceCode = <font>'ZZ'<br><br></font></pre></span>
  • Chili Joe 2005-09-07 04:08
    Foon:
    <p>set @sequenceCode = right(cast(cast( @sequenceCode as int) + 101 as varchar(3)), 2)</p>
    <p>or possibly even</p>
    <p>set @sequenceCode = cast(cast( @sequenceCode as int) + 101 as varchar(2))</p>
    <br>
    <br>
    The 1st one would be the solution I would have come up with. For
    fixed-length-char numeric sequences. Easily extendable for larger
    lengths:<br>
    <br>
    3 digits:<br>
    set @sequenceCode = right(cast(cast( @sequenceCode as int) + 1001 as varchar(3)), 3)<br>
    4 digits:<br>
    set @sequenceCode = right(cast(cast( @sequenceCode as int) + 10001 as varchar(3)), 4)<br>
  • Chili Joe 2005-09-07 04:09
    Chili Joe:
    Foon:
    <p>set @sequenceCode = right(cast(cast( @sequenceCode as int) + 101 as varchar(3)), 2)</p>
    <p>or possibly even</p>
    <p>set @sequenceCode = cast(cast( @sequenceCode as int) + 101 as varchar(2))</p>
    <br>
    <br>
    The 1st one would be the solution I would have come up with. For
    fixed-length-char numeric sequences. Easily extendable for larger
    lengths:<br>
    <br>
    3 digits:<br>
    set @sequenceCode = right(cast(cast( @sequenceCode as int) + 1001 as varchar(3)), 3)<br>
    4 digits:<br>
    set @sequenceCode = right(cast(cast( @sequenceCode as int) + 10001 as varchar(3)), 4)<br>
    <br>
    <br>
    WTF, should be<br>

    set @sequenceCode = right(cast(cast( @sequenceCode as int) + 1001 as varchar(4)), 3)<br>

    set @sequenceCode = right(cast(cast( @sequenceCode as int) + 10001 as varchar(5)), 4)<br>
  • Tom 2005-09-07 04:13
    Everyone knows that you should be using lookup tables and select
    statements in SQL. And why on earth would we want to waste disk space
    by storing the lookup table in the database when we can generate it on
    the fly?<br>
    <br>
    <pre>select @sequencenum=isnull(b,@sequencenum) from<br> (select <br> convert(varchar,a)+convert(varchar,b) a,<br> right('0'+convert(varchar,convert(int,convert(varchar,a)+convert(varchar,b))+1),2) b<br> from<br> (select 0 a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a,<br> (select 0 b union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b<br> where a != 0 or b != 0<br> ) c<br> where a=@sequencenum<br><br></pre>
  • RayS 2005-09-07 04:34
    I've had enough of these things that are supposed to be simple being
    difficult. I'm going to invent something to make it easier to make
    simple changes to numbers according to a basic rule.<br>
    <br>
    I shall call it 'maths'.<br>
  • Vicky 2005-09-07 05:44
    Anonymous:
    <span id="PostFlatView"><pre><font>The obvious WTF is that he doesn't handle wrap around case properly. It should be:<br><br>ELSE</font> <font>IF</font> @sequenceCode = <font>'99'</font><br> <font>SET</font> @sequenceCode = <font>'ZZ'<br><br></font></pre></span>
    <br>
    <br>
    Now *that* is what I call brillant! <br>
    <br>
  • Masklinn 2005-09-07 05:56
    dmitriy:
    The WTFGenerator class given by Volmarias does not compile:<br>
    <br>
    dmitriy@si[202]$ javac WTFGenerator.java <br>
    WTFGenerator.java:3: illegal start of expression<br>
    for(int i = 1; i!=100; ;) {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    ^<br>
    WTFGenerator.java:3: illegal start of expression<br>
    for(int i = 1; i!=100; ;) {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    ^<br>
    WTFGenerator.java:7: ';' expected<br>
    }<br>
    ^<br>
    3 errors<br>
    <br>
    Once I remove the errors in the code (those above and others), it
    prints out lines to set the value to 2 if it is 1, to 3 if it is 2, and
    so on. Similarly to some of the other submissions, it increments 99 to
    100.<br>

    <p>Here is a Python (but not pythonic at all) version that does handle 99>00 increment, and does in fact generate any random length incrementing suite behaving as today's WTF does: increment by 1, replace maximum value by "00", pad to 2, anything out of the (]0, upper[) intact.</p>
    <blockquote><pre>def WTFGenerator(maxIndice):
    try:
    maxIndice=int(maxIndice)
    except ValueError:
    while 1:
    print "SET @sequenceCode='ZZ'"

    for i in xrange(1,maxIndice):
    if not i == 1:
    print "ELSE IF @sequenceCode='%02d'"%(i)
    else:
    print "IF @sequenceCode='%02d'"%(i)
    if not i==maxIndice-1:
    print "\tSET @sequenceCode='%02d'"%(i+1)
    else:
    print "\tSET @sequenceCode='00'"</pre></blockquote>
    <p>Please do notice the graceful error handling, and the fact that the output code IS indented as required</p>
  • Masklinn 2005-09-07 06:08
    <P>And a version a slight bit more pythonic (but much more moronic):</P>
    <BLOCKQUOTE><PRE>def WTFGenerator(maxIndice):
    try:
    maxIndice=int(maxIndice)
    except ValueError:
    while 1:
    print "SET @sequenceCode='ZZ'"

    brillant = False
    for i in ["IF @sequenceCode='%02d'\n\tSET @sequenceCode='%02d'"%(i,(i+1)%100) for i in xrange(1,maxIndice)]:
    print "%s%s"%(brillant and "ELSE " or "", i)
    if not brillant:
    brillant=True</PRE></BLOCKQUOTE>
    <P>For the non-python guys: Python doesn't provide the ternary operator (a?b:c) but the "a and b or c" structure provides a close enough emulation as long as "b" is never computed to False.</P>
  • Suomynona 2005-09-07 07:14
    Anonymous:
    <P>In order to prove that I do too much in T-SQL, how about this?:</P>
    <P>set @sequenceCode = right( '0' + cast( cast( @sequenceCode as int) + 1 as varchar(3) ), 2)</P>


    I think we have a winner.
  • Suomynona 2005-09-07 07:15
    Bustaz Kool:
    <p>As far as I can discern, all of the one liners throw an error on invalid input.</p>
    <br>
    <br>
    And that's a good thing, Mr. Spolsky.<br>
    <br>
  • ammoQ 2005-09-07 07:38
    in Oracle, a brillant solution would look like that:<br>
    <br>
    select ltrim(to_char(nvl(min(num),0),'00'))<br>
    &nbsp; into sequenceCode<br>
    &nbsp; from (select rownum as num from all_objects where rownum&lt;100)<br>
    &nbsp; where num&gt;sequenceCode;<br>
    <br>
    The sad part of the story: I've seen programs that <span style="font-style: italic;">really</span> did similar select statements...<br>
  • Suomynona 2005-09-07 07:41
    Anonymous:
    Has anyone here ever remarked on the quality of the forum software?


    Remarked on <b>what</b>?
  • Suomynona 2005-09-07 07:48
    As people continued to complain that the preview didn't always look like the final result, the author of the forum software just dropped the preview button entirely (at least for unregistered users). What a brillant solution!

    Frankly, by now I'm surprised that the URL in my browser's location bar does not contain snippets of SQL.
  • bullestock 2005-09-07 08:06
    Anonymous:
    As people continued to complain that the
    preview didn't always look like the final result, the author of the
    forum software just dropped the preview button entirely (at least for
    unregistered users).
    <br>
    <br>
    I can confirm that it is also gone for registered users. "If you can't fix it, kill it".<br>
  • Rubinho 2005-09-07 09:01
    kipthegreat:
    travisowens:
    <p>So what happens when his database needs to
    support more than 0-100, what about 100,000?  That's a whole month
    of copying and pasting.</p>
    <br>
    <br>
    That's what interns are for!<br>
    <br>
    <br>
    And if the intern had an ounce of sense he'd at least write a code
    generator in [perl|python|VB|whatever] to write out all the cases for
    him!<br>
  • RhythmAddict 2005-09-07 09:33
    <P>
    Anonymous:
    Maurits:
    Prepwork..<BR><BR>CREATE TABLE sequencecodemap<BR>(<BR>&nbsp; &nbsp; this char(2),<BR>&nbsp;&nbsp;&nbsp; next char(2)<BR>)<BR><BR>INSERT INTO sequencecodemap (this, next)<BR>VALUES ('00', '01')<BR><BR>INSERT INTO sequencecodemap (this, next)<BR>VALUES ('01', '02')<BR><BR>...<BR>snip<BR>
    <BR><BR>You're just trying to get into next week's WTF, right?<BR><BR>
    </P>
    <P>hahahha...</P>
    <P>Sometimes my boss and I have conversations like this...</P>
    <P>me: It's a complicated program, there is a lot of business logic</P>
    <P>him:&nbsp; A lot of if/else's, hunh?</P>
    <P>me: yes</P>
    <P>this (and similiar wtf's) make me wonder how many programmers such as this one he is come into contact with over the years.&nbsp; A little scary.[:^)]</P>
  • azaris 2005-09-07 10:39
    Mung Kee:
    I cannot overstate how truly amazed I am at the number of big stupid
    switch statements or if...else statements we see here.&nbsp; Once the
    number of comparisons exceeds a handful, it may be time to start
    thinking about a more pragmatic solution.&nbsp; I think a lot of
    developers lack this process of graduated thought.<br>
    <br>
    <br>
    The problem is, their thought process goes like this:<br>
    <br>
    // Do I have too many cases?<br>
    <br>
    switch (numberOfCases) {<br>
    &nbsp;&nbsp;&nbsp; case 1:<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; // not too many<br>
    &nbsp;&nbsp;&nbsp; case 2:<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; // not too many<br>
    &nbsp;&nbsp;&nbsp; case 3:<br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; // not too many<br>
    ...<br>
  • Enric Naval 2005-09-07 10:39
    public class WTFGenerator {

    public static void main(String[] args) {
    int numberOfDigits;
    try {
    numberOfDigits = Integer.parseInt(args[1]);
    }
    catch (java.lang.NumberFormatException ex) {
    System.out.println("The first argument has to be an integer");
    System.exit(-1);
    }
    if ( numberOfDigits <= 0 ) {
    System.out.println("The first argument has to be a POSITIVE integer");
    System.exit(-2);
    }
    long max = numberOfDigits * 10;
    for(long i = 1; i < max ; i++ ) {
    System.out.println("IF @sequenceCode = '" + padNumber(i,numberOfDigits) + "'");
    System.out.println("\tSET @sequenceCode = '" + padNumber(++i,numberOfDigits) + "'");
    }
    }


    // if num is too short, return num adding zeros at the start
    // if num is length OK, return num
    // if num is too long, return num shortened to adequate length and all digits set to zero
    public String padNumber (long x, int numberOfDigits) {
    String num = Long.toString(x);
    int digitsToPad = numberOfDigits - num.length();
    if ( digitsToPad < 0 ) { // too long
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i = 0; i <= numberOfDigits; i++ ) {
    temp.append("0");
    }
    num = temp.toString();
    }
    else if ( digitsToPad == 0 ) { //length OK
    //num = num;//NOOP
    }
    else if ( digitsToPad > 0 ) { //too short
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i ; i <= digitsToPad ; i++ ) {
    temp.append("0");
    }
    temp.append(num);
    num = temp.toString();
    }
    return num;
    }

    }//end class


    Volmarias, I think this is a bit sad, doing all this function just for generating a copy&paste code :)
  • Mung Kee 2005-09-07 10:43
    Enric Naval:
    public class WTFGenerator {

    public static void main(String[] args) {
    int numberOfDigits;
    try {
    numberOfDigits = Integer.parseInt(args[1]);
    }
    catch (java.lang.NumberFormatException ex) {
    System.out.println("<span style="font-weight: bold;">The first argument has to be an integer</span>");
    System.exit(-1);
    }
    if ( numberOfDigits &lt;= 0 ) {
    System.out.println("<span style="font-weight: bold;">The first argument has to be a POSITIVE integer</span>");
    System.exit(-2);
    }
    long max = numberOfDigits * 10;
    for(long i = 1; i &lt; max ; i++ ) {
    System.out.println("IF @sequenceCode = '" + padNumber(i,numberOfDigits) + "'");
    System.out.println("\tSET @sequenceCode = '" + padNumber(++i,numberOfDigits) + "'");
    }
    }


    // if num is too short, return num adding zeros at the start
    // if num is length OK, return num
    // if num is too long, return num shortened to adequate length and all digits set to zero
    public String padNumber (long x, int numberOfDigits) {
    String num = Long.toString(x);
    int digitsToPad = numberOfDigits - num.length();
    if ( digitsToPad &lt; 0 ) { // too long
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i = 0; i &lt;= numberOfDigits; i++ ) {
    temp.append("0");
    }
    num = temp.toString();
    }
    else if ( digitsToPad == 0 ) { //length OK
    //num = num;//NOOP
    }
    else if ( digitsToPad &gt; 0 ) { //too short
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i ; i &lt;= digitsToPad ; i++ ) {
    temp.append("0");
    }
    temp.append(num);
    num = temp.toString();
    }
    return num;
    }

    }//end class


    Volmarias, I think this is a bit sad, doing all this function just for generating a copy&amp;paste code :)
    <br>
    <br>
    <span id="_ctl0_PostForm_Reply">args[1] is the second argument</span><br>
  • Enric Naval 2005-09-07 10:44
    uh? half of my code has disappeared! Damn the &lt; tag!

    public&nbsp;class&nbsp;WTFGenerator&nbsp;{&nbsp;
    &nbsp;
    &nbsp;&nbsp;public&nbsp;static&nbsp;void&nbsp;main(String[]&nbsp;args)&nbsp;{&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;int&nbsp;numberOfDigits;
    &nbsp;&nbsp;&nbsp;&nbsp;try&nbsp;{
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;numberOfDigits&nbsp;=&nbsp;Integer.parseInt(args[1]);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;catch&nbsp;(java.lang.NumberFormatException&nbsp;ex)&nbsp;{
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.out.println(&quot;The&nbsp;first&nbsp;argument&nbsp;has&nbsp;to&nbsp;be&nbsp;an&nbsp;integer&quot;);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.exit(-1);
    &nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(&nbsp;numberOfDigits&nbsp;&lt;=&nbsp;0&nbsp;)&nbsp;{
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.out.println(&quot;The&nbsp;first&nbsp;argument&nbsp;has&nbsp;to&nbsp;be&nbsp;a&nbsp;POSITIVE&nbsp;integer&quot;);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.exit(-2);
    &nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;long&nbsp;max&nbsp;=&nbsp;numberOfDigits&nbsp;*&nbsp;10;
    &nbsp;&nbsp;&nbsp;&nbsp;for(long&nbsp;i&nbsp;=&nbsp;1;&nbsp;i&nbsp;&lt;&nbsp;max&nbsp;;&nbsp;i++&nbsp;)&nbsp;{&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.out.println(&quot;IF&nbsp;@sequenceCode&nbsp;=&nbsp;'&quot;&nbsp;+&nbsp;padNumber(i,numberOfDigits)&nbsp;+&nbsp;&quot;'&quot;);&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.out.println(&quot;\tSET&nbsp;@sequenceCode&nbsp;=&nbsp;'&quot;&nbsp;+&nbsp;padNumber(++i,numberOfDigits)&nbsp;+&nbsp;&quot;'&quot;);&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;
    &nbsp;&nbsp;}&nbsp;


    &nbsp;&nbsp;//&nbsp;if&nbsp;num&nbsp;is&nbsp;too&nbsp;short,&nbsp;return&nbsp;num&nbsp;adding&nbsp;zeros&nbsp;at&nbsp;the&nbsp;start&nbsp;
    &nbsp;&nbsp;//&nbsp;if&nbsp;num&nbsp;is&nbsp;length&nbsp;OK,&nbsp;return&nbsp;num
    &nbsp;&nbsp;//&nbsp;if&nbsp;num&nbsp;is&nbsp;too&nbsp;long,&nbsp;return&nbsp;num&nbsp;shortened&nbsp;to&nbsp;adequate&nbsp;length&nbsp;and&nbsp;all&nbsp;digits&nbsp;set&nbsp;to&nbsp;zero
    &nbsp;&nbsp;public&nbsp;String&nbsp;padNumber&nbsp;(long&nbsp;x,&nbsp;int&nbsp;numberOfDigits)&nbsp;{&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;String&nbsp;num&nbsp;=&nbsp;Long.toString(x);
    &nbsp;&nbsp;&nbsp;&nbsp;int&nbsp;digitsToPad&nbsp;=&nbsp;numberOfDigits&nbsp;-&nbsp;num.length();
    &nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(&nbsp;digitsToPad&nbsp;&lt;&nbsp;0&nbsp;)&nbsp;{&nbsp;//&nbsp;too&nbsp;long
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StringBuffer&nbsp;temp&nbsp;=&nbsp;new&nbsp;StringBuffer(numberOfDigits);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for&nbsp;(&nbsp;int&nbsp;i&nbsp;=&nbsp;0;&nbsp;i&nbsp;&lt;=&nbsp;numberOfDigits;&nbsp;i++&nbsp;)&nbsp;{
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;temp.append(&quot;0&quot;);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;num&nbsp;=&nbsp;temp.toString();
    &nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;else&nbsp;if&nbsp;(&nbsp;digitsToPad&nbsp;==&nbsp;0&nbsp;)&nbsp;{&nbsp;//length&nbsp;OK
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//num&nbsp;=&nbsp;num;//NOOP
    &nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;else&nbsp;if&nbsp;(&nbsp;digitsToPad&nbsp;&gt;&nbsp;0&nbsp;)&nbsp;{&nbsp;//too&nbsp;short
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StringBuffer&nbsp;temp&nbsp;=&nbsp;new&nbsp;StringBuffer(numberOfDigits);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for&nbsp;(&nbsp;int&nbsp;i&nbsp;;&nbsp;i&nbsp;&lt;=&nbsp;digitsToPad&nbsp;;&nbsp;i++&nbsp;)&nbsp;{
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;temp.append(&quot;0&quot;);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;temp.append(num);
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;num&nbsp;=&nbsp;temp.toString();
    &nbsp;&nbsp;&nbsp;&nbsp;}
    &nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;num;
    &nbsp;&nbsp;}&nbsp;

    }//end&nbsp;class
  • Enric Naval 2005-09-07 10:47
    Mung Kee:
    <br>
    <br>
    <span id="_ctl0_PostForm_Reply">args[1] is the second argument</span><br>


    Oppps, I was thinking in bash, where $0 is the script name in the command line, and $1 is the first argument...
  • Enric Naval 2005-09-07 10:54
    hum, what about the pre tag? It works, doesn't it?

    <blockquote>
    <pre>
    public class WTFGenerator {

    public static void main(String[] args) {
    int numberOfDigits;
    try {
    numberOfDigits = Integer.parseInt(args[1]);
    }
    catch (java.lang.NumberFormatException ex) {
    System.out.println("The first argument has to be an integer");
    System.exit(-1);
    }
    if ( numberOfDigits LESS OR EQUAL TO 0 ) {
    System.out.println("The first argument has to be a POSITIVE integer");
    System.exit(-2);
    }
    long max = numberOfDigits * 10;
    for(long i = 1; i LESS OR EQUAL TO max ; i++ ) {
    System.out.println("IF @sequenceCode = '" + padNumber(i,numberOfDigits) + "'");
    System.out.println("\tSET @sequenceCode = '" + padNumber(++i,numberOfDigits) + "'");
    }
    }


    // if num is too short, return num adding zeros at the start
    // if num is length OK, return num
    // if num is too long, return num shortened to adequate length and all digits set to zero
    public String padNumber (long x, int numberOfDigits) {
    String num = Long.toString(x);
    int digitsToPad = numberOfDigits - num.length();
    if ( digitsToPad LESS THAN 0 ) { // too long
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i = 0; i LESS OR EQUAL TO numberOfDigits; i++ ) {
    temp.append("0");
    }
    num = temp.toString();
    }
    else if ( digitsToPad == 0 ) { //length OK
    //num = num;//NOOP
    }
    else if ( digitsToPad GREATER THAN 0 ) { //too short
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i ; i LESS OR EQUAL TO digitsToPad ; i++ ) {
    temp.append("0");
    }
    temp.append(num);
    num = temp.toString();
    }
    return num;
    }

    }//end class

    </pre>
    </blockquote>
  • Enric Naval 2005-09-07 10:55
    thisForumSoftwareHaters++;
  • SDL 2005-09-07 10:55
    <P>in ansi sql:</P>
    <P>select substr(to_char(to_number(:sequenceCode)+1),'099'),1,2) from ...</P>
  • Masklinn 2005-09-07 11:00
    Anonymous:
    As people continued to complain that the preview didn't always look like the final result, the author of the forum software just dropped the preview button entirely (at least for unregistered users). What a brillant solution! Frankly, by now I'm surprised that the URL in my browser's location bar does not contain snippets of SQL.

    <P>Wouldn't be possible, since even the frigging page number is transferred via POST instead of GET (which means that you can't reload the page without getting some retarded "hey mate I have to resend informations to the server, are you ok with that?" popup from your browser...</P>
    Enric Naval:
    Volmarias, I think this is a bit sad, doing all this function just for generating a copy&amp;paste code :)

    <P>While I don't know if it's worth the hassle in Java for an average programmer, it's much faster to write such a crapcode generator in Python/Ruby/Perl than actually writing the crapcode by hand... even taking the fubared corner cases in account.</P>
    <P>AND you can leave the generator somewhere in case you need to "extend" the feature/code !</P>
  • Enric Naval 2005-09-07 11:01
    [blockquote]
    [pre]
    public class WTFGenerator {

    public static void main(String[] args) {
    int numberOfDigits;
    try {
    numberOfDigits = Integer.parseInt(args[1]);
    }
    catch (java.lang.NumberFormatException ex) {
    System.out.println("The first argument has to be an integer");
    System.exit(-1);
    }
    if ( numberOfDigits LESS OR EQUAL TO 0 ) {
    System.out.println("The first argument has to be a POSITIVE integer");
    System.exit(-2);
    }
    long max = numberOfDigits * 10;
    for(long i = 1; i LESS OR EQUAL TO max ; i++ ) {
    System.out.println("IF @sequenceCode = '" + padNumber(i,numberOfDigits) + "'");
    System.out.println("\tSET @sequenceCode = '" + padNumber(++i,numberOfDigits) + "'");
    }
    }


    // if num is too short, return num adding zeros at the start
    // if num is length OK, return num
    // if num is too long, return num shortened to adequate length and all digits set to zero
    public String padNumber (long x, int numberOfDigits) {
    String num = Long.toString(x);
    int digitsToPad = numberOfDigits - num.length();
    if ( digitsToPad LESS THAN 0 ) { // too long
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i = 0; i LESS OR EQUAL TO numberOfDigits; i++ ) {
    temp.append("0");
    }
    num = temp.toString();
    }
    else if ( digitsToPad == 0 ) { //length OK
    //num = num;//NOOP
    }
    else if ( digitsToPad GREATER THAN 0 ) { //too short
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i ; i LESS OR EQUAL TO digitsToPad ; i++ ) {
    temp.append("0");
    }
    temp.append(num);
    num = temp.toString();
    }
    return num;
    }

    }//end class

    [/pre]
    [/blockquote]

    ( sorry for filling the forum with horribly formatted posts )
  • Masklinn 2005-09-07 11:06
    Enric Naval:
    ( sorry for filling the forum with horribly formatted posts )

    <p>I guess you wanted</p>
    <blockquote><pre>public class WTFGenerator {
    public static void main(String[] args) {
    int numberOfDigits;
    try {
    numberOfDigits = Integer.parseInt(args[1]);
    }
    catch (java.lang.NumberFormatException ex) {
    System.out.println("The first argument has to be an integer");
    System.exit(-1);
    }
    if ( numberOfDigits LESS OR EQUAL TO 0 ) {
    System.out.println("The first argument has to be a POSITIVE integer");
    System.exit(-2);
    }
    long max = numberOfDigits * 10;
    for(long i = 1; i LESS OR EQUAL TO max ; i++ ) {
    System.out.println("IF @sequenceCode = '" + padNumber(i,numberOfDigits) + "'");
    System.out.println("\tSET @sequenceCode = '" + padNumber(++i,numberOfDigits) + "'");
    }
    }
    // if num is too short, return num adding zeros at the start
    // if num is length OK, return num
    // if num is too long, return num shortened to adequate length and all digits set to zero
    public String padNumber (long x, int numberOfDigits) {
    String num = Long.toString(x);
    int digitsToPad = numberOfDigits - num.length();
    if ( digitsToPad LESS THAN 0 ) { // too long
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i = 0; i LESS OR EQUAL TO numberOfDigits; i++ ) {
    temp.append("0");
    }
    num = temp.toString();
    }
    else if ( digitsToPad == 0 ) { //length OK
    //num = num;//NOOP
    }
    else if ( digitsToPad GREATER THAN 0 ) { //too short
    StringBuffer temp = new StringBuffer(numberOfDigits);
    for ( int i ; i LESS OR EQUAL TO digitsToPad ; i++ ) {
    temp.append("0");
    }
    temp.append(num);
    num = temp.toString();
    }
    return num;
    }
    }//end class </pre></blockquote>
    <p>BTW you're not outputting "ELSE IF", but only "IF"s</p>
  • JRG 2005-09-07 11:07
    <P>
    Masklinn:
    While I don't know if it's worth the hassle in Java for an average programmer, it's much faster to write such a crapcode generator in Python/Ruby/Perl than actually writing the crapcode by hand... even taking the fubared corner cases in account.
    </P>
    <P>I use excel or Ultraedit to generate my copy/paste code...</P>
  • Enric Naval 2005-09-07 11:14
    JRG:
    <P>
    Masklinn:
    While I don't know if it's worth the hassle in Java for an average programmer, it's much faster to write such a crapcode generator in Python/Ruby/Perl than actually writing the crapcode by hand... even taking the fubared corner cases in account.
    </P>
    <P>I use excel or Ultraedit to generate my copy/paste code...</P>


    I use bash, but I can't pad strings with it, because I can't do a length() operation on a variable.

    http://www.tldp.org/LDP/abs/html/comparison-ops.html
  • Enric Naval 2005-09-07 11:16
    Yes, that's it. Can you tell how you did it?
  • ammoQ 2005-09-07 11:17
    Enric Naval:
    JRG:
    <p>
    Masklinn:
    While I don't know if it's worth the hassle in Java for
    an average programmer, it's much faster to write such a crapcode
    generator in Python/Ruby/Perl than actually writing the crapcode by
    hand... even taking the fubared corner cases in account.
    </p>
    <p>I use excel or Ultraedit to generate my copy/paste code...</p>


    I use bash, but I can't pad strings with it, because I can't do a length() operation on a variable.

    http://www.tldp.org/LDP/abs/html/comparison-ops.html


    <pre>
    [erich@localhost erich]$ wtf="hello world"
    [erich@localhost erich]$ length=$(echo $wtf|wc -c)
    [erich@localhost erich]$ echo $length
    12
    [erich@localhost erich]$
    </pre>
  • emptyset 2005-09-07 11:17
    <P><FONT face="Courier New" size=2>see, nobody has the big question here.&nbsp; why the hell is this function needed?&nbsp; in what context is it used?&nbsp; HOW DID IT GET THERE GOSH DARN IT.</FONT></P>
  • emptyset 2005-09-07 11:19
    <P>
    Enric Naval:
    uh? half of my code has disappeared!
    </P>
    <P><FONT face="Courier New" size=2>brillant.</FONT></P>
  • ammoQ 2005-09-07 11:20
    ammoQ:
    Enric Naval:
    JRG:
    <p>
    Masklinn:
    While I don't know if it's worth the hassle in Java for
    an average programmer, it's much faster to write such a crapcode
    generator in Python/Ruby/Perl than actually writing the crapcode by
    hand... even taking the fubared corner cases in account.
    </p>
    <p>I use excel or Ultraedit to generate my copy/paste code...</p>


    I use bash, but I can't pad strings with it, because I can't do a length() operation on a variable.

    http://www.tldp.org/LDP/abs/html/comparison-ops.html


    <pre>[erich@localhost erich]$ wtf="hello world"<br>[erich@localhost erich]$ length=$(echo $wtf|wc -c)<br>[erich@localhost erich]$ echo $length<br>12<br>[erich@localhost erich]$<br></pre>
    <br>
    <br>
    WTF... like a Pentium doing divisions... fast, but wrong.<br>
    The right way to do it:<br>
    <br>
    <pre>
    [erich@localhost erich]$ wtf="hello world"
    [erich@localhost erich]$ length=$(echo -n $wtf|wc -c)
    [erich@localhost erich]$ echo $length
    11
    [erich@localhost erich]$
    </pre>
  • Masklinn 2005-09-07 11:20
    Enric Naval:
    Yes, that's it. Can you tell how you did it?

    <P>Yes. You have to create a post, then immediatly switch to HTML view (clic on the HTML tab at the bottom), and use
    <BLOCKQUOTE><PRE><PRE>your code</PRE>;</PRE></BLOCKQUOTE>
    <P></P>
    <P>The important thing is to remember switching to HTML right out of the bat, or you'll get a much fubared code when you will.</P>
  • Masklinn 2005-09-07 11:28
    <P>Gosh, that piece of crap managed to interpret my l33t escaped characters... let's try again</P>
    <P>edit: leet, can't even write HTML entities, that sorry excuse for an editor creates HTML from them...</P>
    <P>[blockquote][pre]your_code[/pre][/blockquote]</P>
    <P>Replace [ and ] by lt and gts.</P>
  • Bellinghman 2005-09-07 11:31
    "I can confirm that it is also gone for registered users. "If you can't fix it, kill it"."

    *cough*

    This may be good news - the author of the code may be realising that there are problems, and maybe coming to the conclusion that they need fixing. I hope so.

    Or perhaps Alex disabled the button.
  • Enric Naval 2005-09-07 11:35
    The HTML tab, which, of course, &lt;b&gt;doesn't appear on Opera, so I have to swith to IE&lt;/b&gt;. ouch!
  • Enric Naval 2005-09-07 11:37
    The HTML tab, which, of course, <b>doesn't appear on Opera, so I have to swith to IE</b>. ouch!
  • brazzy 2005-09-07 11:40
    Anonymous:
    <br>
    Sometimes my boss and I have conversations like this...
    <p>me: It's a complicated program, there is a lot of business logic</p>
    <p>him:&nbsp; A lot of if/else's, hunh?<br>
    <br>
    </p>
    <p>An all-too-true statement I read in some book (IIRC "<i>Refactoring: Improving the Design of Existing Code</i>"):<br>
    </p>
    <p>"There is very little that is less logical than so called 'business logic'"<br>
    </p>
    <p>Usually it's a huge bunch of special cases that ultimately exist
    because of marketing (such as allowing salesmen to offer "special
    conditions" to convince uncertain customers).<br>
    </p>
  • Masklinn 2005-09-07 11:54
    Enric Naval:
    The HTML tab, which, of course, <B>doesn't appear on Opera, so I have to swith to IE</B>. ouch!

    <p>Well, it's not *THAT* bad, I can't even get the frigging captcha to appear with Firefox, which mean that I can't even post if i'm not using MSIE (me thinks i'm going to create an account just for that, because it's getting more and more annoying)</p>
  • joost 2005-09-07 12:12
    ammoQ:
    Enric Naval:
    JRG:
    <p>
    Masklinn:
    While I don't know if it's worth the hassle in Java for
    an average programmer, it's much faster to write such a crapcode
    generator in Python/Ruby/Perl than actually writing the crapcode by
    hand... even taking the fubared corner cases in account.
    </p>
    <p>I use excel or Ultraedit to generate my copy/paste code...</p>


    I use bash, but I can't pad strings with it, because I can't do a length() operation on a variable.

    http://www.tldp.org/LDP/abs/html/comparison-ops.html


    <pre>[erich@localhost erich]$ wtf="hello world"<br>[erich@localhost erich]$ length=$(echo $wtf|wc -c)<br>[erich@localhost erich]$ echo $length<br>12<br>[erich@localhost erich]$<br></pre>
    <br>
    <br>
    WTF?<br>
    <br>
    <font size="4"><span style="font-family: courier new;">$ wtf="hello world, i can count"</span><br style="font-family: courier new;">
    <span style="font-family: courier new;">$ echo ${#wtf}</span></font><br>
    <br>
    RTFM! LOL, LMAO<br>
  • mizhi 2005-09-07 13:54
    Ytram:
    <span id="_ctl0_PostForm_Reply">On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.
    <br>
    <br>
    You mean leverage the word 'leverage', right?<br>
    </span>
    <br>
    <br>
    I occasionally leverage my foot into someone's posterior.<br>
  • ammoQ 2005-09-07 15:00
    joost:
    ammoQ:
    Enric Naval:
    JRG:
    <p>
    Masklinn:
    While I don't know if it's worth the hassle in Java for
    an average programmer, it's much faster to write such a crapcode
    generator in Python/Ruby/Perl than actually writing the crapcode by
    hand... even taking the fubared corner cases in account.
    </p>
    <p>I use excel or Ultraedit to generate my copy/paste code...</p>


    I use bash, but I can't pad strings with it, because I can't do a length() operation on a variable.

    http://www.tldp.org/LDP/abs/html/comparison-ops.html


    <pre>[erich@localhost erich]$ wtf="hello world"<br>[erich@localhost erich]$ length=$(echo $wtf|wc -c)<br>[erich@localhost erich]$ echo $length<br>12<br>[erich@localhost erich]$<br></pre>
    <br>
    <br>
    WTF?<br>
    <br>
    <font size="4"><span style="font-family: courier new;">$ wtf="hello world, i can count"</span><br style="font-family: courier new;">
    <span style="font-family: courier new;">$ echo ${#wtf}</span></font><br>
    <br>
    RTFM! LOL, LMAO<br>
    <br>
    <br>
    nice to know! guess you are right with that RTFM...<br>
  • Bustaz Kool 2005-09-07 15:19
    Anonymous:
    Bustaz Kool:

    <P>As far as I can discern, all of the one liners throw an error on invalid input.</P>
    <P>
    <BR><BR>And that's a good thing, Mr. Spolsky.<BR><BR>
    </P>
    <P>Well, now we're getting into my mantra, "What are the requirements?".&nbsp; </P>
    <P>The original solution (the original WTF) was able to gracefully handle any input that did not conform to a two digit number and return the input intact.&nbsp; Is that the requirement?&nbsp; I can't tell from the info given.&nbsp; All we really have is the de facto results of the code as given.</P>
    <P>It is, to me, better to validate the data and only operate on valid inputs and return the character string.&nbsp; </P>
  • vDave420 2005-09-07 16:16
    joost:
    Alex Papadimoulis:
    [...] and is best enjoyed if you've
    never programmed anything in T-SQL in your life before. [...]<br>
    <br>

    <p></p><pre>SET @number = INT(@sequenceCode)<br>IF @number &gt; 0<br> SET @number = @number + 1<br> IF @number = 100 <br> SET @number = 0<br> IF @number &lt; 10<br> &nbsp;SET @sequenceCode = '0' + STR(@number)<br> ELSE<br> SET @sequenceCode = STR(@number)<br>DROP DATABASE<br><br></pre>
    <br>
    <br>
    Hahahaha<br>
    <br>
    I hope at least _one_ person tried this...<br>
    <br>
    Lol<br>
    <br>
    &nbsp;&nbsp; -dave-<br>
  • vDave420 2005-09-07 16:55
    Enric Naval:
    thisForumSoftwareHaters++;
    <br>
    <br>
    ++thisForumSoftwareHaters++<br>
    <br>
    Count me in on this as well...<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp; -dave-<br>
    <br>
  • makomk 2005-09-07 17:10
    Anonymous:

    <P>Wouldn't be possible, since even the frigging page number is transferred via POST instead of GET (which means that you can't reload the page without getting some retarded "hey mate I have to resend informations to the server, are you ok with that?" popup from your browser...</P>


    Ah, the biggest WTF of all. Still, it could be worse - it could use session data to keep track of what page you're viewing (shudder)
  • joost 2005-09-08 04:09
    <P><FONT face="Courier New">Re: guess you are right with that RTFM</FONT></P>
    <P><FONT face="Courier New">In principle, yes, but the man page of bash is such a mess, you can't really blame anyone for missing 'obvious' things like getting the number of characters in a variable, or parameter in sh-speak. You have your positional parameters, parameter expansion, brace expansion, tilde expansion, arithmetic expansion and whatnot.&nbsp;This thing ${#var} is buried in parameter expansion. :-S Of course you have to find all this out in that crappy 'less' pager. </FONT></P>
  • cholmok 2005-09-08 10:05
    <PRE>My one liner:</PRE><PRE><STRONG><FONT color=#006400>SET @sequenceCode = <BR>CAST(RIGHT('00' + CAST((CAST(@sequenceCode AS INT) + 1) AS VARCHAR(5)),2) AS CHAR(2))</FONT></STRONG></PRE><PRE>It works, it is type correct, and accounts for all stated cases.</PRE>
  • Robert 2005-09-08 10:06
    <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
    <P><EM>@sequenceCode = @sequenceCode + 1<BR><BR>Do I win?</EM><BR></P></BLOCKQUOTE>
    <P>BZZZZZZZZZZZZZZZZT! Thank you for playing, but 99 becomes 0 in the original code, and values above 99 remain unchanged.</P>
    <P>I don't know this language, but in C++, it would be</P>
    <P>if (sequenceCode &lt; 100)</P>
    <P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sequenceCode = (sequenceCode + 1) % 100</P>
    <P>to duplicate the exact functionality of the original.</P>
  • ammoQ 2005-09-08 10:19
    Anonymous:
    <blockquote style="margin-right: 0px;" dir="ltr">
    <p><em>@sequenceCode = @sequenceCode + 1<br><br>Do I win?</em><br></p></blockquote>
    <p>BZZZZZZZZZZZZZZZZT! Thank you for playing, but 99 becomes 0 in the original code, and values above 99 remain unchanged.</p>
    <p>I don't know this language, but in C++, it would be</p>
    <p>if (sequenceCode &lt; 100)</p>
    <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sequenceCode = (sequenceCode + 1) % 100</p>
    <p>to duplicate the exact functionality of the original.</p>
    <br>
    <br>
    Not exactly, since sequenceCode is a string and all values except "00", "01", ..., "99" stay unchanged.<br>
  • ML 2005-09-08 10:43
    <P>Lovely. I guess '00' stays '00'. Glory!</P>
    <P>&nbsp;</P>
    <P>ML</P>
  • ammoQ 2005-09-08 11:18
    Anonymous:
    <p>Lovely. I guess '00' stays '00'. Glory!</p>
    <br>
    <br>
    WTF, you are right!!!<br>
  • Anonymous Coward 2005-09-08 11:24
    <P>"00 stays 00" -- not a special case, that's the bug in the original code that had to be fixed.</P>
    <P>&nbsp;</P>
  • Anonymous Coward 2005-09-09 06:51
    Alex Papadimoulis:
    <PRE> <FONT color=#000099>IF</FONT> @sequenceCode = <FONT color=#990000>'01'</FONT>
    <FONT color=#000099>SET</FONT> @sequenceCode = <FONT color=#990000>'02'</FONT>
    <FONT color=#000099>ELSE</FONT> <FONT color=#000099>IF</FONT> @sequenceCode = <FONT color=#990000>'02'</FONT>
    <FONT color=#000099>SET</FONT> @sequenceCode = <FONT color=#990000>'03'</FONT>
    <FONT color=#000099>ELSE</FONT> <FONT color=#000099>IF</FONT> @sequenceCode = <FONT color=#990000>'03'</FONT>
    <FONT color=#000099>SET</FONT> @sequenceCode = <FONT color=#990000>'04'</FONT>
    <FONT color=#000099>ELSE</FONT> <FONT color=#000099>IF</FONT> @sequenceCode = <FONT color=#990000>'04'</FONT>
    <FONT color=#000099>SET</FONT> @sequenceCode = <FONT color=#990000>'05'</FONT>
    <FONT color=#000099>ELSE</FONT> <FONT color=#000099>IF</FONT> @sequenceCode = <FONT color=#990000>'05'</FONT>
    <FONT color=#000099>SET</FONT> @sequenceCode = <FONT color=#990000>'06'</FONT>
    <FONT color=#000099>ELSE</FONT> <FONT color=#000099>IF</FONT> @sequenceCode = <FONT color=#990000>'06'</FONT>
    <FONT color=#000099>SET</FONT> @sequenceCode = <FONT color=#990000>'07'</FONT>
    <FONT color=#009900>-- snip --</FONT></PRE>
    <P>
    </P>
    <P>Doesn't anyone see it?</P>
    <P>This was written by a SENIOR programmer.</P>
    <P>That guy first learned to code in COBOL, and&nbsp;his minions still&nbsp;suffer the consequences.</P><PRE>&nbsp;</PRE>
  • Otis Mukinfus 2005-09-09 09:42
    Anonymous:

    <P>Best.&nbsp; Solution.&nbsp; Evar.&nbsp; Truly leveraging the full dynamic range of your core competencies as well as&nbsp;the available technology.</P>
    <P>&nbsp;</P>
    <P>On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.&nbsp; Not even business speak, but real actual person to person colloquial conversation.&nbsp; Apparently they're too good for the word 'use.'</P>
    <P>
    </P>
    <P>That's an extremely proactive definitization of&nbsp; leveraging the word leverage in bizTalk</P>
    <P>How long did it take you to definitize you reply?</P>
  • countrydave 2005-09-09 15:14
    <FONT face="Courier New" size=2>SET @sequenceCode =<BR>&nbsp; CASE<BR>&nbsp;&nbsp;&nbsp; WHEN LEN(@sequenceCode) &lt;&gt; 2 OR ISNUMERIC(@sequenceCode) &lt;&gt; 1 THEN @sequenceCode<BR>&nbsp;&nbsp;&nbsp; WHEN @sequenceCode IN ('00', '99') THEN '00'<BR>&nbsp;&nbsp;&nbsp; ELSE REPLACE(STR(CONVERT(int, @sequenceCode) + 1, 2, 0), ' ', '0')<BR>&nbsp; END</FONT>
  • joost 2005-09-10 15:02
    countrydave:
    <font face="Courier New" size="2">SET @sequenceCode =<br>&nbsp; CASE<br>&nbsp;&nbsp;&nbsp; WHEN LEN(@sequenceCode) &lt;&gt; 2 OR ISNUMERIC(@sequenceCode) &lt;&gt; 1 THEN @sequenceCode<br>&nbsp;&nbsp;&nbsp; WHEN @sequenceCode IN ('00', '99') THEN '00'<br>&nbsp;&nbsp;&nbsp; ELSE REPLACE(STR(CONVERT(int, @sequenceCode) + 1, 2, 0), ' ', '0')<br>&nbsp; END</font>
    <br>
    <br>
    Great, I just learned there's a difference between programming and writing stored procedures. Way to shift my paradigm!<br>
  • 1111111111111111111111111111111111111111111111111111111111111111 2005-09-10 16:20
    Test
  • limelight 2005-09-14 18:16
    Seeing as how everyone else has taken a shot at this, I thought I would
    throw in my own code. It will leave the value the same if it is not
    numeric, less than 1 or greater than 99. It will make the value "00" if
    it is equal to 99. Otherwise, it will increment the value by 1 and will
    also pad with zeros if the return value is less than 10.<br>
    <br>
    <br>
    <span style="color: rgb(0, 128, 0);">-- first - validate that the input is actually a number</span><br>
    <span style="color: rgb(0, 0, 255);">if</span> <span style="color: rgb(255, 20, 147);">isnumeric</span>(@sequenceCode) = 1 <span style="color: rgb(0, 128, 0);">--note, cannot leave off the "= 1" as T-SQL will not accept "if isnumeric(@sequenceCode)" as a valid boolean expression</span><br>
    &nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 0, 255);">begin</span><br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 128, 0);">-- make a temp variable so we don't have to keep casting</span><br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 0, 255);">declare </span>@sequenceCodeAsInt <span style="color: rgb(0, 0, 255);">int</span><br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 0, 255);">&nbsp;set </span>@sequenceCodeAsInt = <span style="color: rgb(255, 20, 147);">cast</span>(@sequenceCode <span style="color: rgb(0, 0, 255);">as int</span>)<br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 128, 0);">-- check value is in range (greater than zero and less than 100)</span><br>
    <span style="color: rgb(0, 0, 255);">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;if </span>@sequenceCodeAsInt &gt; 0 <span style="color: rgb(0, 0, 255);"><span style="color: rgb(169, 169, 169);">and</span> </span>@sequenceCodeAsInt &lt; 100<br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 0, 255);">begin</span><br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 128, 0);">--increment value by one, cast to varchar and choose last two digits (which will be '00' for 100)</span><br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 0, 255);">set </span>@sequenceCode = <span style="color: rgb(255, 20, 147);">right</span>(<span style="color: rgb(255, 20, 147);">cast</span>(@sequenceCodeAsInt + 1 <span style="color: rgb(0, 0, 255);">as varchar</span>),2)<br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 128, 0);">--left pad with '0' if length is less than 2</span><br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 0, 255);">set </span>@sequenceCode = <span style="color: rgb(255, 20, 147);">replicate</span>(<span style="color: rgb(255, 0, 0);">'0'</span>,2 - <span style="color: rgb(255, 20, 147);">len<span style="color: rgb(0, 0, 0);">(@</span></span>sequenceCode)) + @sequenceCode<br>
    &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<span style="color: rgb(0, 0, 255);">end</span><br style="color: rgb(0, 0, 255);">
    <span style="color: rgb(0, 0, 255);">&nbsp;&nbsp; &nbsp;end</span>
  • sux0r mi pen3r 2005-10-04 01:14
    rofl[pi]
  • DrCode 2005-10-09 13:02
    ammoQ:
    joost:
    ammoQ:
    Enric Naval:
    I use bash, but I can't pad strings with it, because I can't do a length() operation on a variable.


    <pre>...<br>...$ length=$(echo $wtf|wc -c)<br>...<br>
    <br></pre>
    <font size="4"><span style="font-family: courier new;"></span><font size="2"><span style="font-family: courier new;">$ echo ${#wtf}</span></font></font><font size="2"><br><br>
    RTFM! LOL, LMAO<br></font>
    <br>
    <br>
    nice to know! guess you are right with that RTFM...<br>
    <br><br>you could also use:<br><br>$ length=`expr length $wtf`<br><br>but if the goal is to pad the number, why not just use<br><br>$ printf "%.2d" $x<br><br>$ help printf<br><br>Cheers,<br>
  • logic 2008-12-08 17:26
    I'm a few years late on this, but how about this?:

    -- 1-Time Initialization!
    CREATE TABLE Letters (Item VARCHAR(25) NOT NULL)

    INSERT INTO Letters VALUES ('0')
    INSERT INTO Letters VALUES ('1')
    INSERT INTO Letters VALUES ('2')
    INSERT INTO Letters VALUES ('3')
    INSERT INTO Letters VALUES ('4')
    INSERT INTO Letters VALUES ('5')
    INSERT INTO Letters VALUES ('6')
    INSERT INTO Letters VALUES ('7')
    INSERT INTO Letters VALUES ('8')
    INSERT INTO Letters VALUES ('9')
    GO
    CREATE VIEW TwoLetters AS
    SELECT First.Item AS FirstLetter,
    Second.Item AS SecondLetter
    FROM Letters First INNER JOIN Letters Second
    ON First.Item + Second.Item <> '00'
    GO
    CREATE VIEW AddOneToTwoLetters AS
    SELECT FirstLetter AS OriginalFirstLetter,
    SecondLetter AS OriginalSecondLetter,
    FirstLetter,
    CAST(SecondLetter AS INT) + 1 AS SecondLetter
    FROM TwoLetters
    GO
    CREATE VIEW AddOneToTwoLetters2 AS
    SELECT OriginalFirstLetter,
    OriginalSecondLetter,
    FirstLetter,
    SecondLetter
    FROM AddOneToTwoLetters
    WHERE SecondLetter < 10
    GO
    CREATE VIEW AddOneToTwoLetters3 AS
    SELECT OriginalFirstLetter,
    OriginalSecondLetter,
    CAST(FirstLetter AS INT) + 1 AS FirstLetter,
    0 AS SecondLetter
    FROM AddOneToTwoLetters
    WHERE SecondLetter = 10
    GO
    CREATE VIEW AddOneToTwoLetters4 AS
    SELECT OriginalFirstLetter, OriginalSecondLetter, FirstLetter, SecondLetter
    FROM AddOneToTwoLetters2
    UNION ALL
    SELECT OriginalFirstLetter, OriginalSecondLetter, FirstLetter, SecondLetter
    FROM AddOneToTwoLetters3
    GO
    CREATE VIEW AddOneToTwoLetters5 AS
    SELECT OriginalFirstLetter,
    OriginalSecondLetter,
    CAST(FirstLetter AS VARCHAR(2)) + CAST(SecondLetter AS VARCHAR(2)) AS Value
    FROM AddOneToTwoLetters4
    GO
    CREATE VIEW AddOneToTwoLetters6 AS
    SELECT CAST(OriginalFirstLetter AS VARCHAR(1)) + CAST(OriginalSecondLetter AS VARCHAR(1)) AS FromValue,
    Value
    FROM AddOneToTwoLetters5
    WHERE LEN(Value) < 3
    UNION ALL
    SELECT '99', '00'

    -- Now run this to increment @sequenceCode
    SET NOCOUNT ON

    BEGIN TRY
    DROP TABLE #LookupTable
    END TRY
    BEGIN CATCH
    END CATCH

    SELECT *
    INTO #LookupTable
    FROM AddOneToTwoLetters6

    ALTER TABLE #LookupTable
    ADD UNIQUE (FromValue)

    BEGIN TRY
    INSERT INTO #LookupTable VALUES (@sequenceCode, @sequenceCode)
    END TRY
    BEGIN CATCH
    -- This will never happen.
    END CATCH

    SET NOCOUNT OFF

    SELECT @sequenceCode = Value
    FROM #LookupTable
    WHERE FromValue = @sequenceCode

    DROP TABLE #LookupTable


    This code also handles the '00' case -- the original sequence of 'IF' statements didn't contain a case for it, so its value should remain unchanged. :-)