SQL技术问询：如何将True值存入列及筛选clients表符合条件的电话号码

Hey there! Let's break down your two SQL tasks step by step:

First off, keep in mind that boolean types can behave a bit differently across databases, but here are the common approaches for both updating existing rows and inserting new ones:

更新现有行 #

If you need to set a specific column to True for existing records (either all rows or a filtered subset):

-- Works for PostgreSQL and MySQL (MySQL treats TRUE as an alias for 1)
UPDATE your_table_name
SET target_column = TRUE
WHERE your_filter_condition; -- Omit this line if you want to update every row

-- For SQL Server (uses BIT type where 1 = True, 0 = False)
UPDATE your_table_name
SET target_column = 1
WHERE your_filter_condition;

插入新行 #

If you're adding a new row with True in the target column:

-- PostgreSQL/MySQL
INSERT INTO your_table_name (target_column, column1, column2)
VALUES (TRUE, 'value1', 'value2');

-- SQL Server
INSERT INTO your_table_name (target_column, column1, column2)
VALUES (1, 'value1', 'value2');

Quick note: MySQL doesn't have a native boolean type under the hood—it uses TINYINT(1) where 1 maps to TRUE and 0 to FALSE. PostgreSQL supports proper BOOLEAN values, and SQL Server relies on the BIT type for boolean-like logic.

To take the three phone columns (number1, number2, number3) and turn them into individual rows while only keeping those with exactly 10 characters, the most cross-database friendly method is using UNION ALL. If you're using a database that supports UNPIVOT (like SQL Server), we can cover that too.

通用方法（UNION ALL） #

This works across MySQL, PostgreSQL, SQL Server, and most other databases:

SELECT 
    client_name,
    phone_number
FROM (
    -- Unpivot the three columns into rows
    SELECT client_name, number1 AS phone_number FROM clients
    UNION ALL
    SELECT client_name, number2 AS phone_number FROM clients
    UNION ALL
    SELECT client_name, number3 AS phone_number FROM clients
) AS unpivoted_phones
-- Filter for 10-digit numbers, exclude NULLs
WHERE 
    -- Use LENGTH() for MySQL/PostgreSQL, LEN() for SQL Server
    LENGTH(phone_number) = 10
    AND phone_number IS NOT NULL;

SQL Server专用（UNPIVOT语法） #

If you're working with SQL Server, you can use the built-in UNPIVOT operator for cleaner code:

SELECT 
    client_name,
    phone_number
FROM clients
UNPIVOT (
    phone_number FOR phone_columns IN (number1, number2, number3)
) AS unpvt
WHERE 
    LEN(phone_number) = 10
    AND phone_number IS NOT NULL;

Important reminder: If your phone columns are stored as numeric types (like INT or BIGINT), you'll need to cast them to a string first to check length (since numeric types don't preserve leading zeros, which are often part of phone numbers). For example:

• MySQL: CAST(number1 AS CHAR)
• PostgreSQL: number1::TEXT
• SQL Server: CAST(number1 AS VARCHAR(20))

内容的提问来源于stack exchange，提问作者GADI ROSALES