December 1, 2016 at 8:58 am
I'm trying to make a script where I can add leading zeros to numbers less than 7. I have it almost completed. My brain just doesn't seem to be functioning correctly.
SELECT Med_Rec_Nbr
FROM Primary_Summary
where LEN(Med_Rec_Nbr) < 7
That will select the numbers I need. I just help trying to put leading zeros up to 7 in front of my results. If anyone can help me I would so appreciate it.
December 1, 2016 at 9:05 am
Is the Med_Rec_Nbr a varchar? If it's an integer or other numeric type, you won't be able to add leading zeros.
Here's an example:
SELECT Nbr,
RIGHT(REPLICATE('0',7)+Nbr,7)
FROM (VALUES('1'),
('12'),
('123'),
('1234'),
('12345'),
('123456'),
('1234567'))x(Nbr);
December 1, 2016 at 9:07 am
I tend do use the RIGHT function. For example, we have something called "branches" which should be displayed as two digit integer values. Therefore, if I need then ensure branches 0-9 are two part i do:
RIGHT('0' + CAST(B@ AS VARCHAR(2)),2) AS Branch --Note that B@ is an INTEGER within the Table, hence the CAST.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 1, 2016 at 9:07 am
declare @Primary_Summary table (Med_Rec_Nbr int)
insert into @Primary_Summary (Med_Rec_Nbr) VALUES (1234567),(123456),(12345),(2345678)
SELECT RIGHT(CONCAT('0000000',Med_Rec_Nbr),7)
FROM @Primary_Summary
where LEN(Med_Rec_Nbr) < 7
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2016 at 9:24 am
I forgot to mention Med_Rec_Nbr is a nvarchar. I had the cast in my original just trying to do a little more efficient. Thanks for all the helpful replies I did mark the one that worked best for me.
December 1, 2016 at 10:24 am
SELECT ps.Med_Rec_Nbr, REPLACE(STR(ps.Med_Rec_Nbr,7),' ','0')
FROM @Primary_Summary AS ps
This approach also makes it obvious if you got a number larger than expected, rather than truncating to 7 characters.
Wes
(A solid design is always preferable to a creative workaround)
December 1, 2016 at 10:48 am
whenriksen (12/1/2016)
SELECT ps.Med_Rec_Nbr, REPLACE(STR(ps.Med_Rec_Nbr,7),' ','0')FROM @Primary_Summary AS ps
This approach also makes it obvious if you got a number larger than expected, rather than truncating to 7 characters.
It's also slower. It's up to 5 times slower than the other options presented.
Quick performance test:
SELECT TOP (1000000)
ISNULL(CAST( ABS(CHECKSUM(NEWID())) % POWER(10,((ABS(CHECKSUM(NEWID()))%9)+1)) AS nvarchar(10)), N'') AS Med_Rec_Nbr
INTO Primary_Summary
FROM sys.all_columns a, sys.all_columns;
--SET STATISTICS IO ON;
GO
DECLARE @Dummy nvarchar(10);
PRINT '-------------------------------------';
PRINT 'Dry Run';
PRINT '-------------------------------------';
SET STATISTICS TIME ON;
SELECT @Dummy = Med_Rec_Nbr FROM Primary_Summary WHERE LEN(Med_Rec_Nbr) < 7;
SET STATISTICS TIME OFF;
PRINT '-------------------------------------';
PRINT 'REPLACE(STR(ps.Med_Rec_Nbr,7),'' '',''0'')';
PRINT '-------------------------------------';
SET STATISTICS TIME ON;
SELECT @Dummy = REPLACE(STR(Med_Rec_Nbr,7),' ','0') FROM Primary_Summary WHERE LEN(Med_Rec_Nbr) < 7;
SET STATISTICS TIME OFF;
PRINT '-------------------------------------';
PRINT 'RIGHT(CONCAT(''0000000'',Med_Rec_Nbr),7)';
PRINT '-------------------------------------';
SET STATISTICS TIME ON;
SELECT @Dummy = RIGHT(CONCAT('0000000',Med_Rec_Nbr),7) FROM Primary_Summary WHERE LEN(Med_Rec_Nbr) < 7;
SET STATISTICS TIME OFF;
PRINT '-------------------------------------';
PRINT 'RIGHT(REPLICATE(''0'',7)+Nbr,7)';
PRINT '-------------------------------------';
SET STATISTICS TIME ON;
SELECT @Dummy = RIGHT(REPLICATE('0',7)+Med_Rec_Nbr,7) FROM Primary_Summary WHERE LEN(Med_Rec_Nbr) < 7;
SET STATISTICS TIME OFF;
PRINT '-------------------------------------';
--SET STATISTICS IO OFF;
GO 5
DROP TABLE Primary_Summary;
December 1, 2016 at 11:24 am
Another option:
SELECT @Dummy = CASE WHEN LEN( Med_Rec_Nbr ) < 7
THEN RIGHT('000000' + Med_Rec_Nbr,7)
ELSE Med_Rec_Nbr
END
FROM Primary_Summary
Performance is very close to the other proposals, and it doesn't truncate values longer than 7.
Edited: Forgot to remove Where clause
Wes
(A solid design is always preferable to a creative workaround)
December 1, 2016 at 11:36 am
whenriksen (12/1/2016)
Another option:
SELECT @Dummy = CASE WHEN LEN( Med_Rec_Nbr ) < 7THEN RIGHT('000000' + Med_Rec_Nbr,7)
ELSE Med_Rec_Nbr
END
FROM Primary_Summary
WHERE LEN( Med_Rec_Nbr ) < 7;
Performance is very close to the other proposals, and it doesn't truncate values longer than 7.
Why are you using a CASE when you have a WHERE?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply