SQL中实现VARCHAR转INT并运用CASE WHEN提取字段后三位

Got it, let's tackle these two SQL tasks with clear, practical examples. I'll use standard SQL syntax here (adjustments noted for specific databases where needed).

1. VARCHAR转INT并结合CASE WHEN语句处理逻辑 #

Often, you'll have VARCHAR fields storing numeric values (like status codes) that need to be converted to INT for numerical comparisons or categorization. Here's how to do it:

Basic Example #

Suppose we have a user_data table with a VARCHAR column status_code (values like '1', '2', '3'). We'll convert it to INT and use CASE WHEN to create human-readable labels:

SELECT
  id,
  status_code,
  -- Convert VARCHAR to INT first, then use CASE WHEN for exact matches
  CASE CAST(status_code AS INT)
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Suspended'
    ELSE 'Unknown'
  END AS status_label,
  -- Use converted INT for range-based checks
  CASE
    WHEN CAST(status_code AS INT) BETWEEN 1 AND 2 THEN 'Valid Status'
    ELSE 'Invalid Status'
  END AS status_validity
FROM user_data;

Handling Non-Numeric Values #

If your VARCHAR column might contain non-numeric characters, use a "safe" conversion function to avoid errors:

• SQL Server: TRY_CAST(status_code AS INT)
• PostgreSQL: TRY_TO_NUMBER(status_code, '999')
• MySQL: CAST(IF(status_code REGEXP '^[0-9]+$', status_code, NULL) AS INT)

Example with safe conversion:

SELECT
  id,
  status_code,
  CASE TRY_CAST(status_code AS INT)
    WHEN 1 THEN 'Active'
    ELSE 'Unknown'
  END AS status_label
FROM user_data;

2. 提取字段最后3位数字，用CASE表达式创建INT类型新字段 #

To extract the last 3 digits of a field (e.g., transaction IDs, order numbers) and create a new INT field with CASE logic, follow these steps:

Basic Example #

Let's use a transaction_data table with a VARCHAR column transaction_id (values like 'TXN789', 'ORD123456'). We'll extract the last 3 characters, convert to INT, and apply CASE logic:

SELECT
  transaction_id,
  -- Extract last 3 chars, convert to INT, then categorize with CASE
  CASE CAST(RIGHT(transaction_id, 3) AS INT)
    WHEN 000 THEN 'Test Transaction'
    WHEN 100 THEN 'Refund'
    WHEN 200 THEN 'Purchase'
    ELSE 'Other Transaction'
  END AS transaction_category,
  -- Create a standalone INT field with the last 3 digits
  CAST(RIGHT(transaction_id, 3) AS INT) AS transaction_suffix
FROM transaction_data;

Ensuring Valid Numeric Extraction #

If the last 3 characters might not be numeric, add a check to only convert valid digits:

SELECT
  transaction_id,
  CASE
    -- Verify last 3 chars are numeric before conversion
    WHEN RIGHT(transaction_id, 3) ~ '^[0-9]{3}$' THEN CAST(RIGHT(transaction_id, 3) AS INT)
    ELSE NULL
  END AS valid_numeric_suffix
FROM transaction_data;

(Note: ~ is PostgreSQL's regex operator; use REGEXP in MySQL: RIGHT(transaction_id, 3) REGEXP '^[0-9]{3}$')

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