Alright, let's figure out how to clean those messy strings and convert them to bigint values. The core idea is to strip out all non-numeric characters first—things like slashes (/), asterisks (*), and spaces—then cast the remaining numeric string to bigint. Here are tailored solutions for popular databases:

SQL Server #

You can use a combination of TRANSLATE and REPLACE to efficiently remove all unwanted characters in one go:

SELECT 
    CAST(REPLACE(TRANSLATE(your_column, '/* ', '   '), ' ', '') AS BIGINT) AS cleaned_bigint
FROM your_table;

How this works:

• TRANSLATE maps /, *, and spaces to spaces (since we can easily remove all spaces in one step)
• REPLACE strips out every remaining space
• Finally, CAST converts the pure numeric string to bigint

If you need to handle edge cases where the cleaned string might be empty (to avoid conversion errors), wrap it in NULLIF:

SELECT 
    CAST(
        NULLIF(REPLACE(TRANSLATE(your_column, '/* ', '   '), ' ', ''), '') 
        AS BIGINT
    ) AS cleaned_bigint
FROM your_table;

MySQL / MariaDB #

Use REGEXP_REPLACE to target all non-numeric characters and replace them with nothing:

SELECT 
    CAST(REGEXP_REPLACE(your_column, '[^0-9]', '') AS BIGINT) AS cleaned_bigint
FROM your_table;

The regex [^0-9] matches any character that isn't a digit, so this will wipe out all slashes, asterisks, and spaces in one command.

PostgreSQL #

Similar to MySQL, but you need to add the 'g' flag to REGEXP_REPLACE to ensure global replacement (otherwise only the first non-digit gets removed):

SELECT 
    CAST(REGEXP_REPLACE(your_column, '[^0-9]', '', 'g') AS BIGINT) AS cleaned_bigint
FROM your_table;

All these methods will take your messy input strings (like "0000000008/314282" or "00000000 8159*2693") and turn them into valid bigint values (8314282 and 81592693 respectively).

内容的提问来源于stack exchange，提问作者Moshin Khan