-2

I have a table named Table1 which has a varchar, named Col1. I want to create a Table2 and add a leading "0" in front of the contents of Col1.

How can I add a zero character to the front of a varchar? I have tried several ways but none seems to work.

4444
  • 3,541
  • 10
  • 32
  • 43
Sadat Mainuddin
  • 309
  • 3
  • 7
  • 19
  • show a sample of what you have already done in your code/sql and where you are having the issue. – Tanner Jul 22 '13 at 13:54
  • I did this: select RIGHT('00000000000' + convert (varchar, dbo.[kyc2].walletno), 11) it showed following error: _The multi-part identifier "dbo.kyc2.walletno" could not be bound._ And I could not figured out how I can create a new table. – Sadat Mainuddin Jul 22 '13 at 14:11

3 Answers3

1

Following query will create table2 with leading "0" for col1.

 select '0' + Col1 Col1 into table2
 from table1

If table2 is already created and you want to just populate data.

 insert into table2(Col1)
 select '0' + Col1
 from table1
Vasanth
  • 1,670
  • 1
  • 13
  • 17
  • My table was not created. so I used your first code. But it gives this error: _An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name._ Am I doing something wrong? – Sadat Mainuddin Jul 22 '13 at 14:04
  • Edited my first answer. Need to add alias name for the first column. – Vasanth Jul 22 '13 at 14:25
1

Well, if you want to add 100 zeros, you can do:

select replicate('0', 100)+col1
. . .

If you want leading zeros with a given length, then combine this with right():

select right(replicate('0', 10)+col1, 10)
. . .

If you need to insert these into another table, use insert or update on that table instead of select.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

This should do what you want

insert into newTable select concat('0',col1),  ........... FROM oldTable
Anigel
  • 3,435
  • 1
  • 17
  • 23