Trailing spaces in VARCHAR

I just noticed that many people are misunderstanding the trailing spaces in VARCHAR field. The truth is they're not trimmed automatically. At least in the databases I know, Oracle, Sybase, SQL Server, DB2, MySQL, etc.

Many know that when you save "abc" into char(5), two trailing spaces will be padded. And when you save "abc" into varchar(5), no space will be added.

How about save "abc " into varchar(5)? Well, the database will keep the trailing space. This sounds reasonable. Many make mistakes when copying data from char field to varchar field or changing the char type to varchar type. Remember, you'll need to trim, at least right trim them.

Multiply records

In one of my task, I needed to multiply some records in a table, so that they can have more chances to be picked up in a random draw. I did it in SQL Server, so I'll show it in SQL. It's similar in Oracle.
What I need to do is to Cartesian join the target records with a table with a certain number of records.

Create a test table and insert some records.

create table test_multiply( id int, type varchar(10), comments varchar(100) )
go
insert into test_multiply values ( 1, 'single', 'This row will show only once' )
insert into test_multiply values ( 2, 'times 10', 'This row will repeat 10 times' )
go

Now we're to multiply the second record 10 times, we join the records with a table contains 9 records.

insert into test_multiply
select a.* from test_multiply a
inner join ( select top 9 id from sysobjects ) x on 1=1
where a.type = 'times 10'
go
(9 rows affected)

Check the result.

select id, type, count(*) cnt from test_multiply group by id, type
go
id type cnt
----------- ---------- -----------
1 single 1
2 times 10 10

Similar in Oracle.

insert into test_multiply
select a.* from test_multiply a
inner join ( select 1 from all_objects where rownum < 1="1" type =" 'times">