Data Manipulation

[tr4ga]

Hello,

I'm trying to change some data in database and I'm having some problems.
Scenario: Table A has a columnA with some data A123, B456, C789, 12345.
ColumnA
A123
B456
C789
12345

I'm trying to multiply the digits in the column by 2, leaving the letters as is.
The code I'm using is:

UPDATE Table A

SET
CoulmnA= Replace(CoulmnA,Left(CoulmnA,1),' ')
CoulmnA= CAST(CoulmnA AS INT)
CoulmnA= (AcctNo * 2)
CoulmnA= 'A' & AcctNo
WHERE Left(CoulmnA,1) = 'A'

Does any on know of a simple way to do this.
It will be much appreciated

[CaptainD]

I ran this through query analyzer and it worked.

Declare @tTemp table(iID INT IDENTITY(1,1),
sNumber NVARCHAR(25))
 
Insert into @tTemp (sNumber) Values ('A12345')
 
Update @tTemp set sNumber = LEFT(sNumber, 1)+ '' + Cast(Cast(right(sNumber,LEN(sNumber)-1)as Int)*2 as char(5)) + ''
 
select iID, sNumber from @tTemp
go
 

[tr4ga]

CaptainD,
Thanks for the reply.
It worked but I need to modify it a bit.
The table has a few hundred rows so
1. I need to feed the values in. (Can I substitute a select clause for A12345 in the code)
2. I need to update the field in the same table

My logic was,
1. scroll through the column,
2. decide if the first digit is a character,
if so take it off, multiply the digit section by to, add it back. there is a character in the f

[tr4ga]

CaptainD,
Thanks for the reply.
It worked but I need to modify it a bit.
The table has a few hundred rows so
1. I need to feed the values in. (Can I substitute a select clause for A12345 in the code)
2. I need to update the field in the same column and table

My logic was,
1. scroll through the column,
2. decide if the first digit is a character,
if so take it off,
a. multiply the digit section by 2,
b. add the character back.
If not,
a. multiply by 2
3. Update the field.

Sorry for the previous post.
Thanks for any assistance

[CaptainD]

1. I need to feed the values in. (Can I substitute a select clause for A12345 in the code)

"A12345" is a value in the database table, if there were more rows of data, the sql statement will work the same for each record. I just entered one record into the temp table.

What database are you using? Access, SQL Server???
If it's SQL Server you can use "CASE" for the first character of the record to see if it "is numberic".

ISNUMERIC()
returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

Play with it and then post your code, I'll have a look at it.

[tr4ga]

Captain D
The code worked like a charm.

Update tblDemoTest set AcctNo = LEFT(AcctNo, 1)+ '' + Cast(Cast(right(AcctNo,LEN(AcctNo)-1)as BigInt)*2 as char(15)) + ''

from tblDemoTest

Thanks a million..