473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Adding text to a column!

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.
Dec 6 '06 #1
6 1441
Do you need to input this with a SQL script, or can you do the formatting programmatically on the client/server side?
Dec 6 '06 #2
If it is a varchar, you can do something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.  CASE
  3.   WHEN len(FLD) = 6 THEN '84'+FLD
  4.   WHEN len(FLD) = 5 THEN '840'+FLD
  5.   WHEN len(FLD) = 4 THEN '8400'+FLD
  6.   WHEN len(FLD) = 3 THEN '84000'+FLD
  7.   WHEN len(FLD) = 2 THEN '840000'+FLD
  8.   WHEN len(FLD) = 1 THEN '8400000'+FLD
  9.  END
  10.  
  11. FROM TABLE
  12.  
You may be able to do this with a loop, but with only 6 digits, this would work.

Best regards,

Michael C. Gates
Dec 6 '06 #3
almaz
168 Expert 100+
...
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:
Expand|Select|Wrap|Line Numbers
  1. update Cards
  2. set CardNumber = '84' + isnull(replicate('0', 9 - len('84') - len(CardNumber)), '') + CardNumber
Dec 7 '06 #4
iburyak
1,017 Expert 512MB
I would do it this way:

[PHP]update table_name set column_name = convert(varchar(20), 840000000 + convert(int, column_name))[/PHP]
Dec 7 '06 #5
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.
Dec 7 '06 #6
iburyak
1,017 Expert 512MB
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.
Dec 7 '06 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Sue | last post by:
On code-behind page: (attributes set programatically for each of these elements) linkbutton added to tablecell textbox added to tablecell tablecells added to tablerow tablerow added to table...
3
by: Claudio Lapidus | last post by:
Hello Now perhaps this is a bit dumb, but... I just populated a new table via \copy. After that, I realize that perhaps is a good thing to have a row identifier in it, so I try clapidus=>...
3
by: MS | last post by:
What's the best way to "store" and display a value in a text box that changes from day to day. An example of this would be where the name of the user is manually typed in after using the datbase,...
2
by: Clayton Hamilton | last post by:
I have a DataGrid on a webform bound to a Datasource and can successfully use <ItemTemplate> to create edit/update/cancel functionality for user maintenance of data. I use separate logic to delete...
0
by: Chris Millar | last post by:
I have a user control that i wish to extend to change the date when the user selects the numeric up down button. The code explains itself, hope someone can help. any ideas appreaciated.. ...
0
by: Andrew | last post by:
If item is a ListViewItem and str is a string, why do the following two lines not have the same effect ? item.SubItems.Add(new ListViewItem.ListViewSubItem()).Text = str;...
3
by: Jim Heavey | last post by:
Trying to figure out the technique which should be used to add rows to a datagrid. I am thinking that I would want an "Add" button on the footer, but I am not quite sure how to do that. Is that...
6
by: Rudy | last post by:
Hi all, I know this is easy, just can't seem to get it. I have a windows form, and a text box, with a value already in it. I need to add that value to a table. It's just one value, so the entire...
1
by: Ben Ramsey | last post by:
I'm using PostgreSQL on a Windows 2000 server and cygwin. All is set up just fine. The database works just fine, and ASP is connecting to the database through a DSN just fine. The only problem I...
7
by: =?Utf-8?B?V2ViQnVpbGRlcjQ1MQ==?= | last post by:
I'm adding subheadings to a gridview. Each sub head has a few link buttons. I'm adding the controls in the rowdatabound event code follows: sorry about the length here. I have to be missing...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.