October 20, 2014 at 9:50 am
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
October 20, 2014 at 9:55 am
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
October 20, 2014 at 10:01 am
the number might go above 100 and if so i dont want 00 in front of that. is there a way around that
October 20, 2014 at 10:20 am
What's the datatype of the column in the target table?
John's solution handles everything up to 999, not just 100.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 20, 2014 at 10:28 am
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.
October 20, 2014 at 10:32 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 20, 2014 at 10:35 am
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
October 20, 2014 at 10:41 am
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)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 20, 2014 at 12:00 pm
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
October 20, 2014 at 3:38 pm
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;
October 20, 2014 at 10:06 pm
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