April 3, 2020 at 12:49 pm
I am using this statement:
Select @data = @data + RIGHT('00000000000' +
CAST( (SUBSTRING(STR(@qtramt1),1,11)) as SYSNAME), 14)
Here the elements:
@data is a row of data fields
@qtramt1 is the amount defined as @qtramt1 numeric(11,0),
The results that I am getting with the above statement is: 0000 94500
I need to obtain this: 00000000094500
What do I need to do?
April 3, 2020 at 2:48 pm
It looks like this issue could be due to the use of STR. The optional length argument using STR is 10, meaning it will pad some additional spaces for that length.
Try the following as an example:
DECLARE @qtramt1 numeric (11, 0) = 94500;
SELECT STR(@qtramt1);
If you're always expecting your result to be a length of 14 with leading 0s, could you try the following?
DECLARE @Data sysname;
DECLARE @qtramt1 numeric (11, 0) = 94500;
SET @Data = N'0';
SELECT @Data = RIGHT(CONCAT(REPLICATE('0', 14), @Data + CAST(@qtramt1 AS nvarchar (11))), 14);
SELECT @Data;
April 3, 2020 at 2:59 pm
Is the original value of @qtramt1 = 94500 ??? Just making sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2020 at 3:19 pm
@qtramt1 = 94500 This just a sample. It is defined as @qtramt1 numeric(11,0). But I need it to expand to 14 positions and zero fill. This is just one of many fields in the DATA row. Coming from:
@last_name, @first_name,@middle_name, @qtramt1
April 3, 2020 at 6:01 pm
From what you've posted, you start off wanting to treat this data element as a string, but then you're storing it as a numeric. You also don't know that a field in SQL refers to part of a data element, so you use the old terms from COBOL files. Display formatting in a tiered architecture, such as SQL is done in presentation layer, and not in the database.. I'm also bothered by the name "qtramt1" since the index implies a group. And SQL does not use repeated groups, such as arrays.. I would've thought you would have something like this in your DDL (which you didn't bother to post for us)
CREATE TABLE Foobar
(...
something_qtr_code CHAR(14) NOT NULL
DEFAULT '00000000000000'
CHECK (something_qtr_code LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
What you posted clearly cannot be an amount because it is a character string. I changed what is called the attribute property to "_code" to conform to ISO 11179 naming rules.
And set up including things that we are trying to do, much better to add those leading zeros in an input layer of your tiered architecture. Unfortunately, your mindset is still back in COBOL, where all values are shown as character strings.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 3, 2020 at 7:56 pm
declare @qtramt1 numeric(11,0)=94500;
select format(cast(@qtramt1 as bigint), 'd14');
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 3, 2020 at 8:10 pm
I am trying to create a row in a table that is created as: CREATE TABLE tax_table ( Data char(275)). Within this table the "Data" consist of 4 fields that are last_name first_name middle_name amount_of _quarttax which is selected using select script and stored in a tmp file; then using this script:
DECLARE @data varchar(275),
@last_name varchar(20),
@first_name varchar(12),
@middle_name varchar(1),
@qtramt1 numeric(11,0)
open tmp
fetch tmp into
@last_name, @first_name,@middle_name,@qtramt1
while @@FETCH_STATUS = 0
BEGIN
Select @data = @last_name + @first_name + @middle_name
Select @data = @data + RIGHT('00000000000' +
CAST( (SUBSTRING(STR(@qtramt1),1,11)) as SYSNAME), 14)
INSERT INTO MRB_StateUnemployment
SELECT @data
fetch tmp into
@last_name, @first_name,@middle_name,@qtramt1
END
Here is the results:
Batch Mille A0000 2329242
This what I need:
Batch Mille A00000002329242
I hope this clears up what I am talking about.
April 3, 2020 at 8:16 pm
scdecade: that worked
select format(cast(@qtramt1 as bigint), 'd14');
Thank you
April 3, 2020 at 8:36 pm
scdecade: that worked
select format(cast(@qtramt1 as bigint), 'd14');
Thank you
Lordy, no (Sorry, scdecade). That's one of the slowest methods possible. I'll be back to prove it. Just don't use FORMAT!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2020 at 8:56 pm
Using FORMAT is the slowest method even for something this simple. Even STR, which is pretty bad, isn't nearly as bad (except that it will silently truncate and right pad with zeroes... see the following article on all that https://www.sqlservercentral.com/articles/hidden-formatting-troubles-with-str-sql-spackle ). Format averages between 40 and 45 times slower than just about any other kind of formatting you can dream up. FORMAT is one of those things that cause "Death of performance by a thousand tiny cuts". Until they fix it for performance, don't use it... period. There is no justification for using something so wrong even if it's for a small number of rows.
Here's a test of multiple different methods. Get the "fnTally" function at the link in my signature line below.
--============================================================================================================
-- Setup a million row test table.
--============================================================================================================
--===== If the test table already exists, drop it to make reruns in SSMS easier.
SET STATISTICS TIME OFF;
IF OBJECT_ID('tempdb..#Data','U') IS NOT NULL DROP TABLE #Data
;
--===== Create a test table of random constrained values from 1 to 100,000.
-- This isn''t a part of the solution... we''re just building some test data here.
-- Don''t let the million rows scare ya... this takes less than a second on my laptop.
SELECT qtramt1 = CONVERT(DECIMAL(11,0),ABS(CHECKSUM(NEWID())%100000)+1)
INTO #Data
FROM Util.dbo.fnTally(1,1000000) --Get this from the link in my signature line below)
;
GO
--============================================================================================================
-- Test Multiple Methods ("String of Zeros" and "Replicate Zeros" methods take turns winning)
-- Total runtime of these tests is < 19 Seconds on my LapTop.
--============================================================================================================
--===== This first test is just to "Prime the pump" and does not meet requirements.
RAISERROR('--===== BASELINE (Implict Conversion Only) =====================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = qtramt1
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== String of Zeros method =================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('00000000000000'+CONVERT(VARCHAR(14),qtramt1),14)
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== REPLICATE Zeros method =================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(REPLICATE('0',14)+CONVERT(VARCHAR(14),qtramt1),14)
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== REPLICATE Zeros/LEN method =============================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = REPLICATE('0',14-LEN(qtramt1))+CONVERT(VARCHAR(14),qtramt1)
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== Math method ============================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(100000000000000+qtramt1,14)
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== STUFF/REPLICATE method =================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(REPLICATE('0',14),15-LEN(qtramt1),14,qtramt1)
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== REPLACE/STR method =====================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = REPLACE(STR(qtramt1,14,0),' ','0')
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== RIGHT/REPLICATE/RIGHT method ===========================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(REPLICATE('0',14)+RIGHT(qtramt1,14),14)
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== FORMAT method ==========================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(qtramt1,'00000000000000')
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== FORMAT/REPLICATE method ================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(qtramt1,REPLICATE('0',14))
FROM #Data;
SET STATISTICS TIME OFF;
GO
RAISERROR('--===== FORMAT d14 method ================================================',0,0) WITH NOWAIT;
DECLARE @BitBucket VARCHAR(20); --Used to take disk and display times out of the picture.
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(CAST(qtramt1 AS BIGINT), 'd14')
FROM #Data;
SET STATISTICS TIME OFF;
GO
Here are the results from a run on my laptop. I have a 6 core i7 threaded to 12 with 32 GB of RAM and NVME SSDs running SQL Server 2017 Developers Edition and FORMAT still sucks! 😀
(1000000 rows affected)
--===== BASELINE (Implict Conversion Only) =====================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 148 ms.
--===== String of Zeros method =================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 192 ms.
--===== REPLICATE Zeros method =================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 191 ms.
--===== REPLICATE Zeros/LEN method =============================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 291 ms.
--===== Math method ============================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 266 ms.
--===== STUFF/REPLICATE method =================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 277 ms.
--===== REPLACE/STR method =====================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1158 ms.
--===== RIGHT/REPLICATE/RIGHT method ===========================================
SQL Server Execution Times:
CPU time = 204 ms, elapsed time = 210 ms.
--===== FORMAT method ==========================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 7828 ms, elapsed time = 8171 ms.
--===== FORMAT/REPLICATE method ================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 7828 ms, elapsed time = 8190 ms.
--===== FORMAT d14 method ================================================
SQL Server Execution Times:
CPU time = 7156 ms, elapsed time = 7415 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2020 at 9:02 pm
I am trying to create a row in a table that is created as: CREATE TABLE tax_table ( Data char(275)). Within this table the "Data" consist of 4 fields that are last_name first_name middle_name amount_of _quarttax which is selected using select script and stored in a tmp file; then using this script:
Maybe I'm not understanding the purpose of the tmp file. If you have all of the columns necessary why store in a 'tmp' file?
Lordy, no. That's one of the slowest methods possible. I'll be back to prove it. Just don't use FORMAT!
Jeff, maybe so, maybe so. It appears the OP's doing things one at a time here tho. So the penalty for a single case is maybe small.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 3, 2020 at 9:03 pm
Thank you, Jeff.
April 3, 2020 at 9:36 pm
Jeff Moden wrote:Lordy, no. That's one of the slowest methods possible. I'll be back to prove it. Just don't use FORMAT!
Jeff, maybe so, maybe so. It appears the OP's doing things one at a time here tho. So the penalty for a single case is maybe small.
That's what I try to teach about. While the penalty for a single case may be small, it's still a penalty. If you do it the fast way all the time, you don't have penalties to worry about and you don't have to remember two types of code AND you don't ever have to worry about what happens when someone either uses your small stuff code on something bigger or someone decides to make your small stuff bigger. You'll also develop the "habit" of "doing it right" all the time.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2020 at 2:55 am
From what you've posted, you start off wanting to treat this data element as a string, but then you're storing it as a numeric. You also don't know that a field in SQL refers to part of a data element, so you use the old terms from COBOL files. Display formatting in a tiered architecture, such as SQL is done in presentation layer, and not in the database.. I'm also bothered by the name "qtramt1" since the index implies a group. And SQL does not use repeated groups, such as arrays.. I would've thought you would have something like this in your DDL (which you didn't bother to post for us)
CREATE TABLE Foobar
(...
something_qtr_code CHAR(14) NOT NULL
DEFAULT '00000000000000'
CHECK (something_qtr_code LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
What you posted clearly cannot be an amount because it is a character string. I changed what is called the attribute property to "_code" to conform to ISO 11179 naming rules.
And set up including things that we are trying to do, much better to add those leading zeros in an input layer of your tiered architecture. Unfortunately, your mindset is still back in COBOL, where all values are shown as character strings.
It would be a terrible waste of space to store numeric data as a string, especially with leading zeros. Yeah, I know we do such things with SSNs and the like but there are other places where storing INTs and padding them for display makes a lot more sense.
As one example, there might not be a presentation layer. It might be necessary to export to a "Fixed Field Format" for data transmission or to even populate a tape for COBOL inputs like you seem to work with everyday. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2020 at 12:16 pm
SELECT RIGHT('00000000000000' + convert(varchar,@qtramt1),14) Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply