Failure to Convert sql server varchar(max) datatype to numeric

Question

I have a table RAND8122TBL with a column called Value. Before importing into sql server, the column was comprised of numbers. After importation, sql server converts it to varchar(max). With previous tables,I have used the following query to convert to numeric:

Alter Table RAND8122TBL
Alter Column Value Numeric (18,2)

This query runs on and on without changing the to numeric.

Thank you for taking the time to read and respond to this request.

Al

Answer 1

To find the values that does not convert, use this query:

   SELECT Value FROM RNAD8122TBL WHERE try_convert(numeric(18,2), Value) IS NULL  

Answer 2

Hi @Al C
When converting varchar values to numbers (i.e. decimal, numeric, etc.), you need to be careful in order for your varchar value, not contain any digit grouping symbols (i.e. a comma) or any other characters that do not have a meaning as a number.
Check this example:

create table #test (valueToConvert varchar(max))  
insert into #test values('34352.03'),('123,456.7890')  
  
Update #test Set valueToConvert=Replace(valueToConvert,',','')  
  
Alter Table #test  
Alter Column valueToConvert Numeric (18,2)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.