# Convert NvarChar to float **URL:** \ **Category:** Ignition\ **Created:** [August 23, 2023, 4:42pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295 "2023-08-23T16:42:26Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![rigoberto.reyes](https://avatars.discourse-cdn.com/v4/letter/r/65b543/32.png) [@rigoberto.reyes](https://forum.inductiveautomation.com/u/rigoberto.reyes)\ **Post date:** [August 23, 2023, 4:42pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/1 "2023-08-23T16:42:26Z")
Hi there. I need to convert a SQL column from nvarchar (_Weight column)_ to float to excecute some calculations in a a PowerTable. I attach next screen . ![imagen](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/e/7eac7551bc2da4c90a0fda5c5fee61e1137b1863.png) Iยดm using next sentence, but it shows an error: "Error converting data type nvarchar to float" Could you support to solve this issue? Thanks in advance ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [August 23, 2023, 4:46pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/2 "2023-08-23T16:46:21Z")
Oh no. That weight column should have been a float to begin with and named `weight_kg` or similar. If possible I would make this change in the database and not by just converting it to a in your SELECT query. However, I know that's not always possible, so I would say something like (depends on your db) `SELECT CAST(REPLACE(REPLACE(Weight, 'kg', ''), ' ', '') as float)` may work. Basically you need to strip that kg and any white space away before trying to cast it to a float. Syntax may be different depending on your flavor of db. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [August 23, 2023, 4:46pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/3 "2023-08-23T16:46:23Z")
You need to remove the 'kg' from the value before converting. ```auto CAST(REPLACE([Weight], 'kg', '') AS NUMERIC(10, 2)) AS ConvertedWeight ``` ---
**Author:** ![rigoberto.reyes](https://avatars.discourse-cdn.com/v4/letter/r/65b543/32.png) [@rigoberto.reyes](https://forum.inductiveautomation.com/u/rigoberto.reyes)\ **Post date:** [August 23, 2023, 7:51pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/4 "2023-08-23T19:51:50Z")
Hi, thanks for your feedbak, but the sentence shows next message: "Error converting data type nvarchar to numeric." ๐Ÿ˜• ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [August 23, 2023, 8:18pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/5 "2023-08-23T20:18:01Z")
You don't have a number in the database, you have a string. Some people long ago thought that it was a great idea for computers to not care about the variable type - just store the data and let the computer figure it out at runtime. Then they tried that for a while and discovered that it was a bad idea. They invented strong typing so that the programmer would have to make sure everything was right at design time rather than let it fail at run time. New people came along and thought that was a lot of work and slowed down the developers. So they invented automatic type conversion. Many of them figured out pretty quickly that was a bad idea and started calling it Evil Type Coercion. Sometimes there were compiler options to turn off the evil type coercion and force strict typing. New people came along and thought that was a lot of extra work and slowed down the developers. Contracts can't change as quickly as the whims of the dreamers. They invented weakly typed variables and called it Duck Typing. Some people realized that was a bad idea and invented a new language that tries to enforce strict typing over a duck typing language. And then some people thought anonymous types would be a good idea and the pendulum keeps going back and forth. At some point, some really smart people crashed their very expensive vehicle because some of them were using Miles and others were using Kilometers but the code didn't indicate the units. It was made obvious why numbers need units - either as a property of the object or in the variable name. So, you have a variable that has units (a really good thing) but is no longer a number. Much of the infrastructure you are using would be happy to automatically convert a string that contains a number into a number (automatic type conversion) but you don't have a number in a string (or nvarchar). You have a combination of digits and characters. Option 1: - store the number as a number in the database. Give the column a name that is explicit about units (kg) if the units are always the same, or add another column that has the units. Option 2: - store the number as a string in the database. Give the column a name that is explicit about units (kg) if the units are always the same, or add another column that has the units. Option 3: - store the number and the units as a string in a single column in the database. This is what you have shown us. If you do Option 1 then you can SELECT the number directly - no conversions are needed. You will not get the error. If you do Option 2 then you can SELECT the string and it will be converted to a number for you automatically (probably by the database driver in Java, but you don't really care). You will not get the error. If you do Option 3 then you will have to explicitly separate the number part of the column from the non-numeric part of the column (i.e., Parse). If the units are always kg then you can just throw them away in the actual SELECT statement. That is what the examples above are showing you. If you do that then the database server is doing the conversion for you. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [August 23, 2023, 8:29pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/6 "2023-08-23T20:29:04Z")
> [@rigoberto.reyes](#): > > Hi, thanks for your feedbak, but the sentence shows next message: "Error converting data type nvarchar to numeric." > [@MMaynard](#): > > You need to remove the 'kg' from the value before converting. > > ```auto > CAST(REPLACE([Weight], 'kg', '') AS NUMERIC(10, 2)) AS ConvertedWeight > > ``` ~~MMaynard's solution doesn't remove the space in between. And possibly any other spaces you can't see. Try:~~ ```sql CAST(TRIM(REPLACE([Weight], 'kg', '')) AS NUMERIC(10, 2)) AS ConvertedWeight ``` EDIT: Just confirmed in SQLServer 2014 (you also didn't specify what db you're using) that it trims it automatically. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [August 26, 2023, 11:44am UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/7 "2023-08-26T11:44:36Z")
> [@bkarabinchak.psi](#): > > However, I know that's not always possible I would say, it doesn't matter if it's possible; a weight is a _float_, not a string. A weight should have _never_ been made a string. Changing it is the _only_ option. Not changing it has more drawbacks than [trying to use regex to parse HTML](https://stackoverflow.com/a/1732454/1733467) (if you havent seen it already) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [August 28, 2023, 11:40am UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/8 "2023-08-28T11:40:48Z")
You change it and then find some mission critical external service was counting on it being a string (happened to me...) I would recommend changing, just make sure to talk to the people who all touch the system to see if anyone relies on it in it's current state. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 28, 2023, 11:52am UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/9 "2023-08-28T11:52:45Z")
> [@bkarabinchak.psi](#): > > then find some mission critical external service was counting on it This can be dealt with by changing the table's name, fixing the structural problems, then creating a view with the original table name that re-assembles the rows into the old structure. Typical legacy apps won't notice. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [August 28, 2023, 11:55am UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/10 "2023-08-28T11:55:48Z")
Yea that's a good way to handle it and one of the examples I give as to why you should _not_ name your database tables like "weightTable" - you never know when it will need to change object type Lest you be left with a confusingly named view. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [August 28, 2023, 11:09pm UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/11 "2023-08-28T23:09:18Z")
> [@bkarabinchak.psi](#): > > you should _not_ name your database tables like "weightTable" You should name it "machineAWeightKgTable" instead ๐Ÿ™ƒ and combine all your fields into a single one with id, t\_stamp, and weight-with-units all combined: "12023-08-29 08:40:00.00045kg" ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [August 29, 2023, 10:24am UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/12 "2023-08-29T10:24:01Z")
> [@nminchin](#): > > all combined: "12023-08-29 08:40:00.00045kg" See? Then you can regex it! ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 29, 2023, 10:26am UTC](https://forum.inductiveautomation.com/t/convert-nvarchar-to-float/78295/13 "2023-08-29T10:26:51Z")
Until someone puts XML in it.