May 22, 2009 at 8:33 am
I have data that was sent to me as char (8), but I need to insert the data into a new table as a decimal (9,2). There is not a decimal point in the original char (8) data so a decimal point needs to added before the last 2 characters. The char (8) data will always be 8 in length and leading zeros will need to be dropped.
For example:
00487500 needs to be 4875.00
10195190 needs to be 101951.90
The CREATE statements are for the table receiving the data is
CREATE TABLE [dbo].[Master]
(
[ID] [int] NOT NULL,
[Wages] [decimal](9, 2) NOT NULL
)
The original table is
CREATE TABLE [dbo].[ES]
(
[ID] [int] NOT NULL,
[Wages] [char] (8) Null
)
Any suggestions would be appreciated.
Thanks
May 22, 2009 at 1:15 pm
Here's one way:
CREATE TABLE [dbo].[ES]
(
[ID] [int] NOT NULL,
[Wages] [char] (8) Null
)
CREATE TABLE [dbo].[Master]
(
[ID] [int] NOT NULL,
[Wages] [decimal](9, 2) NOT NULL
)
INSERT INTO dbo.ES (
ID,
Wages
)
SELECT
1,
'00487500'
UNION ALL
SELECT
2,
'10195190'
INSERT INTO dbo.[Master] (
ID,
Wages
)
SELECT
id,
/*
STUFF(StringToSeach, StartLocation, CharsToReplace, CharsToInsert)
By using 0 as the CharsToReplace STUFF will insert the desired
character at the specified location.
*/
CONVERT(DECIMAL(9,2), STUFF(Wages, LEN(Wages)-1, 0, '.'))
FROM
dbo.ES AS E
SELECT * FROM dbo.[Master] AS M
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2009 at 1:29 pm
I will try that.
Thank you.
May 22, 2009 at 3:59 pm
CONVERT(DECIMAL(10,2), Wages)/100
_____________
Code for TallyGenerator
May 22, 2009 at 4:05 pm
I was going to show the convert method also, but Sergiy beat me to it. You could also use CAST as in:
CAST(Wages AS decimal(10,2)) / 100
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2009 at 5:03 pm
Jeffrey Williams (5/22/2009)
I was going to show the convert method also, but Sergiy beat me to it. You could also use CAST as in:CAST(Wages AS decimal(10,2)) / 100
Duh, why didn't I think of that solution. Probably better than mine, but worth testing I guess.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply