Saturday, February 25, 2012

Convert nvarchar values to integer

I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.

example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70

as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.

Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.

Thanks

Cast the values as decimal, i.e.: (You don't want integers, that would lose the portion after the decimal.)

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 0001028.99 )
INSERT INTO @.MyTable VALUES ( 222.00 )
INSERT INTO @.MyTable VALUES ( 0000190.89 )
INSERT INTO @.MyTable VALUES ( 2708.99 )
INSERT INTO @.MyTable VALUES ( 00000-50.99 )
INSERT INTO @.MyTable VALUES ( -109.79 )

SELECT MyValues = cast( MyValue AS decimal(10,2))
FROM @.MyTable

MyValues

1028.99
222.00
190.89
2708.99
-50.99
-109.79

No comments:

Post a Comment