問題描述

我在下方的 varchar 中有 Customer_Telephone_Number 值.

I have Customer_Telephone_Number value in varchar in below side.

Customer_Telephone_Number 值:(222)-3333-333

Customer_Telephone_Number value : (222)-3333-333

INSERT INTO DATABASE_1.dbo.CUSTOMER_TABLE 
(
Customer_Telephone_Number
)

Select

CONVERT(BIGINT,Customer_Telephone_Number)

from 
DATABASE_2.DBO.CUSTOMER_TABLE 

如果我嘗試將 Customer_Telephone_Number 值插入到 Database_1 中，我會遇到以下異常.

If ? try to insert Customer_Telephone_Number value to Database_1 , i get below exception.

Error converting data type varchar to bigint.

那么我該如何解決這個問題，或者我可以刪除("和)"來解決問題嗎?

任何幫助將不勝感激.

謝謝.

推薦答案

一個快速而骯臟的方法是:

A quick and dirty way would be:

Select
    CONVERT
    (
        BIGINT,
        REPLACE
        (
            REPLACE
            (
                REPLACE
                (
                    REPLACE
                    (
                        Customer_Telephone_Number,
                        ' ',--Replace white spaces
                        ''
                    ),
                    '-',--Replace dashes
                    ''
                ),
                '(',--Replace open parenthesis
                ''
            ),
            ')',--Replace close parenthesis
            ''
        )
     )
from 
    DATABASE_2.DBO.CUSTOMER_TABLE 

您可以運行以下命令以查看其他潛在字符:

You can run the following in order to see other potential characters:

select Customer_Telephone_Number 
from DATABASE_2.DBO.CUSTOMER_TABLE
where ISNUMERIC
    (
        REPLACE
        (
            REPLACE
            (
                REPLACE
                (
                    REPLACE
                    (
                        Customer_Telephone_Number,
                        ' ',--Replace white spaces
                        ''
                    ),
                    '-',--Replace dashes
                    ''
                ),
                '(',--Replace open parenthesis
                ''
            ),
            ')',--Replace close parenthesis
            ''
        )
    ) = 0

這將為您提供無法轉換為 BIGINT 的電話號碼列表.檢查原因(例如，如果它們中有 .)并像我在示例中所做的那樣在 REPLACE 中添加這些字符.

This will give you a list of telephone numbers that cannot get converted to BIGINT . Check why (for instance if they have a . in them) and add these characters in the REPLACE as i did in the example.

這篇關于Ms Sql 將 varchar 轉換為 Big Int的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！