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.
12,678 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    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
  2. CosmogHong-MSFT 22,626 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