Newbie: Adding Text to a column

[nogimmies]

I have a column named CardNumber in a table named Cards (fromatted 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 Updated column
6 840000006
19 840000019
234 840000234
8521 840008521
45956 840045956

I was hoping to do this with a SQL statement. Hopefully I have provided enough information. Thanks for any help in advance.

Ted Bradley

 

[PHV]

Something like this ?
840000000 + CAST(CardNumber AS INTEGER)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nogimmies]

PHV - Thanks for the quick reply. I don't want to change the column from varchar to integer, but only want to update all the rows to reflect the results shown in the example I gave.

 

[r937]

update cards
   set cardnumber = 
          substring('84000000' 
             from 1 
             for 9 - char_length(cardnumber) )
        || cardnumber

i know exactly what your next reply is going to be -- "i get an error"

your error will be either at the substring function, or at the char_length function, or at the concatenation operator

why?

because you posted in the ANSI SQL forum, and you got an ANSI SQL answer, which might not work in your particular database system (which you neglected to mention)



r937.com | rudy.ca

 

[junbug178]

How about just adding 840000000 to your information in column 1.

 

[PHV]

junbug178, read my suggestion (very first reply) ...