Problem solve Get help with specific problems with your technologies, process and projects.

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.

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 last published in February 2005

Dig Deeper on SQL Server Database Modeling and Design

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.