SQL实现：提取字符串列test中第二个点之前的内容

Hey there! Let's figure out how to extract everything before the second dot (.) in your test string column. The approach varies a bit depending on which SQL dialect you're using, so here are solutions for the most common ones:

MySQL/MariaDB #

The easiest way here is to use the built-in SUBSTRING_INDEX function, which is perfect for this kind of substring extraction based on delimiters.

SELECT 
  test,
  SUBSTRING_INDEX(test, '.', 2) AS result
FROM your_table;

How this works: SUBSTRING_INDEX(str, delim, count) returns the part of str before the count-th occurrence of delim. If there are fewer than 2 dots (like the value "a"), it just returns the entire original string—exactly what you need for your example!

PostgreSQL #

PostgreSQL doesn't have SUBSTRING_INDEX, but we can use array functions or regular expressions to get the same result.

Approach 1: Array Conversion #

Split the string into an array, take the first two elements, then join them back with a dot:

SELECT 
  test,
  CASE 
    WHEN array_length(string_to_array(test, '.'), 1) >= 2 THEN 
      array_to_string((string_to_array(test, '.'))[1:2], '.')
    ELSE test
  END AS result
FROM your_table;

Approach 2: Regular Expression #

Use SUBSTRING with a regex that matches either a string with two dot-separated segments, or a string with no dots at all:

SELECT 
  test,
  SUBSTRING(test FROM '^([^.]+\.[^.]+|[^.]+)$') AS result
FROM your_table;

The regex breakdown:

• ^ = start of string
• [^.]+\.[^.]+ = any characters except dots, followed by a dot, followed by any characters except dots (covers strings with 2+ dots)
• | = OR
• [^.]+ = any characters except dots (covers strings with 0 or 1 dot)
• $ = end of string

SQL Server #

For SQL Server, we can combine CHARINDEX to find the positions of the dots and SUBSTRING to extract the desired portion:

SELECT 
  test,
  CASE 
    WHEN CHARINDEX('.', test, CHARINDEX('.', test) + 1) > 0 THEN
      SUBSTRING(test, 1, CHARINDEX('.', test, CHARINDEX('.', test) + 1) - 1)
    ELSE test
  END AS result
FROM your_table;

Here's the breakdown:

1. CHARINDEX('.', test) finds the position of the first dot
2. CHARINDEX('.', test, first_dot_pos + 1) finds the position of the second dot (starting after the first one)
3. If a second dot exists, we take the substring from the start up to one character before the second dot; if not, we return the original string

All these solutions will produce exactly the output you want:

• Input "a" → Output "a"
• Input "bc.de.fg" → Output "bc.de"
• Input "k.l.o.p" → Output "k.l"

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