increment numbers in sql

  • i have a flat file that's has some numbers in it. eg 001 002 003 004.

    I have set it to int so I no that's why in the table it shows up as 1 2 3 4

    but what I want to do is read in the values right as 001 etc and then increment. whats the best way around this

  • Read them in as integers, do your arithmetic, then cast as a string. Something like this:

    SELECT RIGHT('00' + CAST(1 AS varchar(3)),3)

    John

  • the number might go above 100 and if so i dont want 00 in front of that. is there a way around that

  • What's the datatype of the column in the target table?

    John's solution handles everything up to 999, not just 100.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • There isn't a great way to do this. You'd probably need to think about importing as ints, then transforming with the appropriate number of 0s ahead of things based on the max value imported.

  • Steve Jones - SSC Editor (10/20/2014)


    There isn't a great way to do this. You'd probably need to think about importing as ints, then transforming with the appropriate number of 0s ahead of things based on the max value imported.

    Unless the target column is already a string, in which case it should be imported as a string and no conversion work is necessary.

    Otherwise, it's really the job of the client application to perform the leading-zero formatting of INTs.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • No there is no great way to do this. The question I was wondering with the padding was more that will work well. But I was wondering when it got to 100 would it still put in the 00 which I think it would but I don't want that to happen. Or even 10 plus. I don't want it like 00100 I just want it as 100. Same with say 20 it can't be 0020 needs to be 020.

    I don't know if that is even possible

  • ronan.healy (10/20/2014)


    No there is no great way to do this. The question I was wondering with the padding was more that will work well. But I was wondering when it got to 100 would it still put in the 00 which I think it would but I don't want that to happen. Or even 10 plus. I don't want it like 00100 I just want it as 100. Same with say 20 it can't be 0020 needs to be 020.

    I don't know if that is even possible

    Why did you not believe me when I told you that John's solution would work up to 999?

    Try this, for example:

    declare @x varchar(10) = '00100'

    select RIGHT('00' + CAST(@x as varchar(3)), 3)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Quick thought, use the stuff function

    😎

    USE tempdb;

    GO

    DECLARE @SAMPLE_SIZE INT = 123;

    DECLARE @PADDING CHAR(3) = '000';

    ;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)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    SELECT

    NM.N AS INT_NUM

    ,STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N) AS PADDED_NUM

    FROM NUMS NM;

    Results

    INT_NUM PADDED_NUM

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

    1 001

    2 002

    3 003

    4 004

    5 005

    6 006

    7 007

    8 008

    9 009

    10 010

    11 011

    12 012

    13 013

    14 014

    15 015

    16 016

    17 017

    18 018

    19 019

    20 020

    21 021

    22 022

    23 023

    24 024

    25 025

    26 026

    27 027

    28 028

    29 029

    30 030

    31 031

    32 032

    33 033

    34 034

    35 035

    36 036

    37 037

    38 038

    39 039

    40 040

    41 041

    42 042

    43 043

    44 044

    45 045

    46 046

    47 047

    48 048

    49 049

    50 050

    51 051

    52 052

    53 053

    54 054

    55 055

    56 056

    57 057

    58 058

    59 059

    60 060

    61 061

    62 062

    63 063

    64 064

    65 065

    66 066

    67 067

    68 068

    69 069

    70 070

    71 071

    72 072

    73 073

    74 074

    75 075

    76 076

    77 077

    78 078

    79 079

    80 080

    81 081

    82 082

    83 083

    84 084

    85 085

    86 086

    87 087

    88 088

    89 089

    90 090

    91 091

    92 092

    93 093

    94 094

    95 095

    96 096

    97 097

    98 098

    99 099

    100 100

    101 101

    102 102

    103 103

    104 104

    105 105

    106 106

    107 107

    108 108

    109 109

    110 110

    111 111

    112 112

    113 113

    114 114

    115 115

    116 116

    117 117

    118 118

    119 119

    120 120

    121 121

    122 122

    123 123

  • Why would you use string manipulation when it's easier to work directly with the numbers using some math?

    Here's a modification of Eirikur's code.

    USE tempdb;

    GO

    DECLARE @SAMPLE_SIZE INT = 123;

    DECLARE @PADDING CHAR(3) = '000';

    ;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)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    SELECT

    NM.N AS INT_NUM

    ,STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N) AS PADDED_NUM

    ,RIGHT( 1000 + NM.N, 3) AS MATH_PADDED_NUM

    FROM NUMS NM;

    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
  • Good point Luis! It is also around 30% faster,

    Quick comparison

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @INT_BUCKET INT = 0;

    DECLARE @CHAR_BUCKET CHAR(7) = '';

    DECLARE @TIMING_RESULTS TABLE

    (

    TR_ID INT IDENTITY(1,1) NOT NULL

    ,TR_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())

    ,TR_TX VARCHAR(100) NOT NULL

    );

    DECLARE @SAMPLE_SIZE INT = 1000000;

    DECLARE @PADDING CHAR(7) = '0000000';

    INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('RIGHT( @SAMPLE_SIZE + NM.N, 7)');

    ;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)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    SELECT

    @INT_BUCKET = NM.N -- AS INT_NUM

    ,@CHAR_BUCKET = RIGHT( @SAMPLE_SIZE + NM.N, 7) -- AS MATH_PADDED_NUM

    FROM NUMS NM;

    INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('RIGHT( @SAMPLE_SIZE + NM.N, 7)');

    INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N)');

    ;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)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    SELECT

    @INT_BUCKET = NM.N --AS INT_NUM

    ,@CHAR_BUCKET = STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N) --AS PADDED_NUM

    FROM NUMS NM;

    INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N)');

    SELECT

    TR.TR_TX AS OPERATION

    ,DATEDIFF(MICROSECOND, MIN(TR.TR_TS), MAX(TR.TR_TS)) AS DURATION

    FROM @TIMING_RESULTS TR

    GROUP BY TR.TR_TX

    ORDER BY 2;

    Results

    OPERATION DURATION

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

    RIGHT( @SAMPLE_SIZE + NM.N, 7) 594801

    STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N) 873601

Viewing 11 posts - 1 through 10 (of 10 total)

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