Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
misbah
Starting Member
1 Post |
 Posted - 2007-08-15 : 10:24:16
|
| Hello, I have a table in sql server that has a field with numeric characters, i would like to select some but not all characters from this field and insert it into a new field. The field that i want to get some characters from has other characters that i dont need, It has a lot of zero's next to the numbers that i need. so far i created a stored procedure, but its not working. I want to extract the characters and insert them into a new field in the format "000.00" or "00.00". The values are either hunderths or tenths, which makes it difficult for me to decide which characters to ignore. Please see my SP created so far below: CREATE PROCEDURE dbo.spUpdatePaymentDetails AS UPDATE dbo.PaymentDetails SET NewAmount = SUBSTRING('00' + TransactionAmount, 1, LEN('00' +TransactionAmount) - 2) + '.' + SUBSTRING('00' + TransactionAmount, LEN('00' + TransactionAmount) - 1, 2) GO the character "." is not in the original field so therefore i would like to insert it into the new field. Please help urgently. Tnx |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-15 : 10:29:50
|
can you post your table DDL and some sample data with the required result ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-08-15 : 11:08:47
|
so, NewAmount SHOULD be a DECIMAL(18,2), but in reality it is probably a varchar(20) or some such. Lovely.How about ALTER TABLE PoorDesign ALTER COLUMN NewAmount Decimal(18,2) Now ram in your amounts.[/surly][Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
|
|
sp to extract characters from one field to another