Adding text to a column!

[nogimmies]

I have a column named CardNumber in a table named Cards (formatted as varchar) with numbers ranging from 1 to 65534. I am trying to change all the card numbers to start with 84 and fill with 0's until the card number and finish with 9 characters. So it would look like this.

Old column
6
19
234
8521
45956

Updated column
840000006
840000019
840000234
840008521
840045956


I would like to leave the fromat as varchar. Thanks for any help in advance.

[gateshosting]

Do you need to input this with a SQL script, or can you do the formatting programmaticall y on the client/server side?

[gateshosting]

If it is a varchar, you can do something like this:

SELECT
 CASE
  WHEN len(FLD) = 6 THEN '84'+FLD
  WHEN len(FLD) = 5 THEN '840'+FLD
  WHEN len(FLD) = 4 THEN '8400'+FLD
  WHEN len(FLD) = 3 THEN '84000'+FLD
  WHEN len(FLD) = 2 THEN '840000'+FLD
  WHEN len(FLD) = 1 THEN '8400000'+FLD
 END

FROM TABLE

You may be able to do this with a loop, but with only 6 digits, this would work.

Best regards,

Michael C. Gates

[almaz]

...
I would like to leave the fromat as varchar. Thanks for any help in advance.

Similar to previous post, but I used more general approach:

update Cards
set CardNumber = '84' + isnull(replicate('0', 9 - len('84') - len(CardNumber)), '') + CardNumber

[iburyak]

I would do it this way:

[PHP]update table_name set column_name = convert(varchar (20), 840000000 + convert(int, column_name))[/PHP]

[nogimmies]

iburyak, that appears to have worked. Just to make sure that command didn't change the field type to integer did it? I wanted to leave it as varchar. Thanks to you and all the previous poster for the help.

[iburyak]

1. Update statement doesn't update database structure only values... :)
2. I converted to varchar before doing update so it should be safe.
3. Test it before using in production.