• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2848
  • Last Modified:

convert a varchar to a number in ms sql

Hi, what is the sql statement to convert a varchar to a number.  I'm trying to run a query that grabs all prices that are either less than, more than, or equal to a value but need to convert those values first.  Currently, the prices have commas in them such as 100,000 and I need them to return like 100000.  My database is ms sql server 2000.
3 Solutions
I would do this:    cast(replace([price],',''') as int)

Remove the commas with replace, then use cast to cast the value to an integer.
If the the source column is a varchar with a comma in the text you have to remove the comma before converting it to an int datatype.  

SELECT CAST(REPLACE('100,000',',','') AS int)

You can convert it directly to money datatype:
SELECT CAST('100,000' AS money)

Or to money and then to int
SELECT CAST(CAST('100,000' AS money) AS int)
where column > convert(money,#b#)
in this example - #b# is your value such as 100,000
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now