Ms Sql convert varchar to Big Int

问题:

I have Customer_Telephone_Number value in varchar in below side.

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 

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

Error converting data type varchar to bigint.

So how can i solve this problem or can i remove "(" and ")" to solve problem ?

Any help will greatly appreciated.

Thank you.

回答1:

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

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.