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

Al C 41 Reputation points
2022-08-24T12:06:51.683+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,003 questions
4,388 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 88,546 Reputation points
    2022-08-24T21:55:31.737+00:00

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

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

  2. CosmogHong-MSFT 15,206 Reputation points Microsoft Vendor
    2022-08-25T02:24:21.16+00:00

    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.

    0 comments No comments