44272

SQL Server cast varchar to int

Question:

I have a table that has a column 'Value' that is a varchar. One row puts a '10' in this column. This "number" will need to be added and substracted to, but I can do so directly b/c its a varchar. So, the following gives an error:

update Fields set Value = Value - 1 from Fields f, FTypes ft where ft.Name = 'Field Count' and ft.ID = f.ID_FT and f.ID_Project = 186

GO

How do I cast/convert the value to an int, perform the math, then set as a varchar again?

Answer1:

Martin Smith's point is an excellent one --> If it is only numeric data going in there and you are always going to be doing operations like this, it will save you time and hassle not having to do this conversion work.

That being said you can do -

update Fields set ColumnName = cast( (cast(ColumnName as int) - 1) as varchar(nn)) from Fields f, FTypes ft where ft.Name = 'Field Count' and ft.ID = f.ID_FT and f.ID_Project = 186

where nn is the original definition of your varchar column

Answer2:

You need to use CAST twice - once to make your Value column an INT so you can subtract 1 from it, and then back to a VARCHAR(x):

update dbo.Fields set Value = CAST((CAST(Value AS INT) - 1) AS VARCHAR(20)) from dbo.Fields f inner join dbo.FTypes ft ON ft.ID = f.ID_FT where ft.Name = 'Field Count' and f.ID_Project = 186

Also, I would recommend using the dbo. prefix always, on all your database objects, and I would always argue for the new, ANSI standard JOIN syntax which is more <em>expressive</em> (clearer to read and understand) and helps avoid unwanted cartesian products (by forgetting to specify a JOIN condition in the WHERE clause....)

Recommend

  • Displaying chart data from a database
  • SVN common code with externals
  • geom_map “map_id” reference issue
  • how can i know if my code is Synthesizable? [Verilog]
  • Java Switch Statement Number Varies
  • How to enumerate Azure subscriptions and tenants programmatically?
  • Efficient way to upload multiple images to S3 from iOS
  • how to use asyncio with boost.python?
  • Firestore - Checking The Connection Status Of The Module To The Server
  • Python PIL remove sections of an image based on its colour
  • Can I call custom javascript from an R jupyter notebook
  • SQL Server: +(unary) operator on non-numeric Strings
  • How are 32 bit JavaScript numbers resulting from a bit-wise operation converted back to 64 bit numbe
  • Fraction length
  • Correctly Importing Apache Commons Math Package
  • Run script file on remote server
  • Iterate twice through a DataReader
  • How to call a procedure using NHibernate that returns result from multiple tables?
  • Compare struct to a constant in C
  • Suqueries in select clause with JPA
  • Python cosine function precision [duplicate]
  • vectorized indexing/slicing in numpy/scipy?
  • Rest Services conventions
  • C# fibonacci function returning errors
  • Default parameter as generic type
  • Convert SQLite database to XML
  • WPF Visiblity Binding to Boolean Expression with multiple Variables
  • Conversion from string “a” to type 'Boolean' is not valid
  • D3 get axis values on zoom event
  • Unable to get column index with table.getColumn method using custom table Model
  • Transactional Create with Validation in ServiceStack Redis Client
  • Hardware Accelerated Image Scaling in windows using C++
  • Convert array of 8 bytes to signed long in C++
  • Arrays break string types in Julia
  • Buffer size for converting unsigned long to string
  • Suggestions to manage Login/Logout transitions
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Sorting a 2D array using the second column C++
  • java string with new operator and a literal