Leading zeros

How do I put leading zeros in a field? The field looks like this: '  100'. I want the capability to be able to put in a single zero or multiple zeros. This is what I was trying, and it just doesn't like something:

 set @testnumb = '  200' DECLARE @addzeros integer SET @addzeros = 00000 declare @testnumb char(5) set substring(rtrim(@testnumb,1,1)) = '0' where (@testnumb,1,1) = ' '))

I don't know much about SQL and am trying to learn as I go.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Congratulations for wanting to learn SQL. It is a joyful ride and a wonderful destination. It's like chess: you can learn the basics very quickly (in ten minutes, if you actually believe the title of a popular SQL book), but it takes a lifetime to get good at it.

It's not clear whether you want to permanently update ("put leading zeros in") your column, or simply just show leading zeros when retrieving values with a SELECT.

One thing's for sure, if you want to learn SQL, then put away your DECLAREs and SETs and run your tests on actual tables. What you have there is Transact-SQL, which is a programming language that is a "superset" of SQL. There's no problem in using T-SQL when it's necessary, but if you're learning SQL, try to solve your problems with just SQL.

Okay, let's say you have a CHAR(5) column with various values, like this:

 create table testzeros ( id smallint not null primary key identity , myfield char(5) ) insert into testzeros (myfield) values (' 1') insert into testzeros (myfield) values (' 11') insert into testzeros (myfield) values (' 111') insert into testzeros (myfield) values (' 1111') insert into testzeros (myfield) values ('11111') insert into testzeros (myfield) values (' ') insert into testzeros (myfield) values ('') insert into testzeros (myfield) values (null)

Now let's write a query that extracts data from that table:

 select id , myfield , replace(myfield,' ','0') as replaced from testzeros order by id

This query produces the following results:

 id myfield replaced 1 1 00001 2 11 00011 3 111 00111 4 1111 01111 5 11111 11111 6 0 7 0 8 null null

The REPLACE function accomplishes your objective, by substituting a character '0' for each space character in the column.

Pay particular attention to the results for rows 6, 7 and 8. If your data allows zero-length strings or NULLs, you should always include these in making up a set of test data -- for every query that you write.

This was first published in February 2005

Back to top