Ms Sql 将 varchar 转换为 Big Int

2021-09-16 00:00:00 sql-server bigint varchar

我在下方的 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.

相关文章