Integer convert to varchar but keep a lenth
-
How to code to convert integer to varchar but keep a fixed length?
For example, for integer below:
1,3,12,698
to be converted to varchar:
00001, 00003, 00012, 00698
-
function stuff can achieve this.
- sterling3721 wrote:
function stuff can achieve this.
You may be right, but it's certainly not obvious how you would use it without hard-coding values. Would you care to elucidate?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thank you. It works for me.
-
declare @i as int=16
select right('00000'+convert(varchar,@i),5) -
SELECT STUFF('00000', 5, 1, '1');
00001
SELECT STUFF('00000', 4, 2, '12');
00012
SELECT STUFF('00000', 3, 3, '698');
00698
- adonetok wrote:
SELECT STUFF('00000', 5, 1, '1');
00001
SELECT STUFF('00000', 4, 2, '12');
00012
SELECT STUFF('00000', 3, 3, '698');
00698
lol, I take it that is a joke?
-
This was removed by the editor as SPAM
-
sterling3721 wrote:function stuff can achieve this.
You may be right, but it's certainly not obvious how you would use it without hard-coding values. Would you care to elucidate?
Drew
this is a very typical leading zero problem. I listed the following five ways doing this. Method 5 is the shortest one. Google may provide more hints.
declare @v-2 table (col1 int)
insert into @v-2
values (1), (3), (12), (698)
-- method 1
select STUFF(col1, 1, 0, REPLICATE('0', 5 - LEN(col1)))
from @v-2
-- method 2
select REPLICATE('0', 5 - LEN(col1)) + CAST(col1 AS varchar)
from @v-2
-- method 3
select RIGHT('00000'+ CAST(col1 AS VARCHAR(5)),5)
from @v-2
-- method 4
select FORMAT(col1, '0000#') -- for SQL2012 or later
from @v-2
-- method 5
select REPLACE(STR(col1, 5), ' ', '0')
from @v-2
- sterling3721 wrote:
this is a very typical leading zero problem. I listed the following five ways doing this. Method 5 is the shortest one. Google may provide more hints.
Why on Earth would you want to choose a method just because it is a few characters shorter to write down than another method?!
Normally you would want the method that is most efficient, not the shortest to write down. In which case you would choose your method 3 or the method I initially suggested:
select right('00000'+convert(col1,@i),5) -
I usually don't accept a method claimed to be "most efficient" without data to support. More importantly, I don't like your tone. Could you please not use words like below?
>> Why on Earth; lol, I take it that is a joke?
- sterling3721 wrote:
I usually don't accept a method claimed to be "most efficient" without data to support. More importantly, I don't like your tone. Could you please not use words like below?
>> Why on Earth; lol, I take it that is a joke?
You could of tested it yourself before you gave advice, but here are some tests and results.
Tests:
set nocount on
declare @v table (col1 int)
insert into @v
values (1), (3), (12), (698)
declare @x varchar(50)
declare @SaveSysdate datetime2(7)=sysdatetime()
-- method 1
select @x=STUFF(col1, 1, 0, REPLICATE('0', 5 - LEN(col1)))
from @v
cross apply(select top(100000) n from dbo.tally) x
print concat('method 1 STUFF(col1, 1, 0, REPLICATE(''0'', 5 - LEN(col1))): ', datediff(mcs,@SaveSysdate, sysdatetime()),' mcs')
set @SaveSysdate =sysdatetime()
-- method 2
select @x=REPLICATE('0', 5 - LEN(col1)) + CAST(col1 AS varchar)
from @v
cross apply(select top(100000) n from dbo.tally) x
print concat('method 2 REPLICATE(''0'', 5 - LEN(col1)) + CAST(col1 AS varchar): ', datediff(mcs,@SaveSysdate, sysdatetime()),' mcs')
set @SaveSysdate =sysdatetime()
-- method 3
select @x=RIGHT('00000'+ CAST(col1 AS VARCHAR(5)),5)
from @v
cross apply(select top(100000) n from dbo.tally) x
print concat('method 3 RIGHT(''00000''+ CAST(col1 AS VARCHAR(5)),5): ', datediff(mcs,@SaveSysdate, sysdatetime()),' mcs')
set @SaveSysdate =sysdatetime()
-- method 4
select @x=FORMAT(col1, '0000#') -- for SQL2012 or later
from @v
cross apply(select top(100000) n from dbo.tally) x
print concat('method 4 FORMAT(col1, ''0000#''): ', datediff(mcs,@SaveSysdate, sysdatetime()),' mcs')
set @SaveSysdate =sysdatetime()
-- method 5
select @x=REPLACE(STR(col1, 5), ' ', '0')
from @v
cross apply(select top(100000) n from dbo.tally) x
print concat('method 5 REPLACE(STR(col1, 5), '' '', ''0''): ', datediff(mcs,@SaveSysdate, sysdatetime()),' mcs')
-- method 6 (my inital method)
set @SaveSysdate =sysdatetime()
select @x=right('00000'+convert(varchar,col1),5)
from @v
cross apply(select top(100000) n from dbo.tally) x
print concat('method 6 right(''00000''+convert(varchar,col1),5): ', datediff(mcs,@SaveSysdate, sysdatetime()),' mcs')Results:
method 1 STUFF(col1, 1, 0, REPLICATE('0', 5 - LEN(col1))): 147913 mcs
method 2 REPLICATE('0', 5 - LEN(col1)) + CAST(col1 AS varchar): 122928 mcs
method 3 RIGHT('00000'+ CAST(col1 AS VARCHAR(5)),5): 97944 mcs
method 4 FORMAT(col1, '0000#'): 8475457 mcs
method 5 REPLACE(STR(col1, 5), ' ', '0'): 771554 mcs
method 6 right('00000'+convert(varchar,col1),5): 95945 mcsI asked if it was a joke as the length of the string which varied was hard coded with a different value for each input value! So I thought the method was funny, I laughed, hence "LOL". I'm really still not sure if it was meant as a joke.
Maybe you would like to answer why you would want to use a method just because it's a few characters shorter than another method with no bearing on performance? The shortest method you listed (if I take out the "-- for SQL2012 or later" comment) is FORMAT, which is about 100 times slower than the fastest method!
-
In the same vein as Jonathan, I like to test these type of things out. I do like to take an average of 5 runs. This is on my new "toy". I've included all the methods as Jonathan did plus one of my own "dark horses", which is almost as short as that god-awful FORMAT method.
The following uses a million randomly generated values of up to 3 digits in length and all the tests use exactly the same million values. Having totally random generated values prevents any data "grooving" that can sometimes "skew-up" a performance test. I don't know if it will here but I never take the chance. I've seen it make an 8 minute difference in testing in the past (compared to 22 seconds) and, boy, the poor folks that made the mistake caught a bit of undeserved hell when it all went to prod (undeserved because they got the testing from a supposedly reputable source that doesn't actually know how to test).
--=====================================================================================================================
-- Create the test harness
--=====================================================================================================================
--===== If they exist, drop the temp tables just to make runs in SSMS easier for testing.
IF OBJECT_ID('tempdb..#TestLog') IS NOT NULL DROP TABLE #TestLog;
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;
GO
--===== We'll keep the results of the test runs in this #TestLogTable
CREATE TABLE #TestLog
(
TestName VARCHAR(200)
,DurationMS INT
)
;
--===== This is where we'll keep the random numbers for testing.
-- Even with a million rows, this should easilly fit in memory.
CREATE TABLE #TestTable
(Col1 INT)
;
--===== This creates a million random numbers of 3 digits or less.
-- This prevents the possibility of "grooved" data, which can really "skew" tests up.
INSERT INTO #TestTable
(Col1)
SELECT TOP 1000000
Col1 = ABS(CHECKSUM(NEWID())%1000)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--=====================================================================================================================
-- Execute the different methods proposed and record the duration of each test in MS.
-- All the tests are identical except for the name of each test and the actual formula being tested.
-- We're dumping the results to the @BitBucket variable to eliminate visual return times.
--=====================================================================================================================
GO
-----------------------------------------------------------------------------------------------------------------------
--===== BASELINE
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = Col1
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT ' BASELINE: Col1',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 01
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = STUFF(col1, 1, 0, REPLICATE('0', 5 - LEN(col1)))
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 01: STUFF(col1, 1, 0, REPLICATE(''0'', 5 - LEN(col1)))',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 02
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = REPLICATE('0', 5 - LEN(col1)) + CAST(col1 AS varchar)
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 02: REPLICATE(''0'', 5 - LEN(col1)) + CAST(col1 AS varchar)',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 03
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = RIGHT('00000'+ CAST(col1 AS VARCHAR(5)),5)
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 03: RIGHT(''00000''+ CAST(col1 AS VARCHAR(5)),5)',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 04
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = FORMAT(col1, '0000#') -- for SQL2012 or later
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 04: FORMAT(col1, ''0000#'')',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 05
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = REPLACE(STR(col1, 5), ' ', '0')
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 05: REPLACE(STR(col1, 5), '' '', ''0'')',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 06 - Jonathon's Initial Method
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = right('00000'+convert(varchar,col1),5)
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 06: right(''00000''+convert(varchar,col1),5)',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
-----------------------------------------------------------------------------------------------------------------------
--===== Method 07 - Jeff's additional method
DECLARE @BitBucket VARCHAR(50)
,@StartDT DATETIME
;
SELECT @StartDT = GETDATE();
SELECT @BitBucket = RIGHT(100000+Col1,5)
FROM #TestTable;
INSERT INTO #TestLog
(TestName,DurationMS)
SELECT 'Method 07: RIGHT(100000+Col1,5)',DATEDIFF(ms,@StartDT,GETDATE());
GO 5
--=====================================================================================================================
-- Display the results in order by duration in MS.
--=====================================================================================================================
SELECT TestName
,DurationMS = AVG(DurationMS)
FROM #TestLog
GROUP BY TestName
ORDER BY DurationMS
;Here are the results I got:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thank you for the data. Theoretically, it's slower; based on my software development experience, web application does not fetch 1,000,000 or even 10,000 rows to the front end. I was never bothered to tune such an issue like this in the past five years. I am not offering the best way doing this, I only provide different ways. It's up to the person who asked the question to decide which way to use. In my experience, parameter sniffing is a much more common performance issue than deciding which built-in function performs the best.
- sterling3721 wrote:
thank you for the data. Theoretically, it's slower; based on my software development experience, web application does not fetch 1,000,000 or even 10,000 rows to the front end. I was never bothered to tune such an issue like this in the past five years. I am not offering the best way doing this, I only provide different ways. It's up to the person who asked the question to decide which way to use. In my experience, parameter sniffing is a much more common performance issue than deciding which built-in function performs the best.
If you limit your thinking to the number of rows "fetched" by a web application, then your Web Application will be a whole lot slower and more resource intensive than it should be. It can also become an issue in the future as the scale of the data increases. This forum is littered with such problems.
The reason why your experience says that parameter sniffing (which is actually very important to performance... only when it goes bad is it an issue) is a "much more common problem" is because you and too many people simply discount performance issues based on small row counts, like you just did.
Also, there are other people in the world that don't work with small numbers of rows like you. Yet, they will take your word for it because your code does work. That's when THEY get into trouble. When something only takes milliseconds to run, that's frequently the most important place where milliseconds actually do matter.
And, no... I'm not bad mouthing you... I'm just trying to make you aware of these types of issues which can help you be more valuable in the future. If every piece of code written were just 2 times faster (never mind 4 to 40 times in this case), just imagine how well things would run. And it's just not difficult to figure these things out... especially when at least two people demonstrate it for you. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply