EXPERT RESPONSE
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.
|
