如何在 SQL 中只返回长度不同的字符串的一部分？

How do I only return part of a string with varying lengths in SQL?

提问by JM1

I have a table with only 1 column that contains a string. I am trying to only get the email address. How can I do that? I looked at Substring/Ltrim, etc, but I haven't been able to piece together how to extract only part of the string. I am fairly new to SQL. Thank you for your help!

我有一个只有 1 列包含字符串的表。我试图只获取电子邮件地址。我怎样才能做到这一点？我查看了 Substring/Ltrim 等，但我无法拼凑出如何仅提取部分字符串。我对 SQL 相当陌生。感谢您的帮助！

Column1:

John Smith     Email: [email protected]  Terminate:

Jacqueline Ryan    Email: [email protected]     Terminate:

回答by Joachim Isaksson

Assuming the email is prefixed by Email:and does not contain spaces, you can just take all characters after Email:and before the next space (or end of string);

假设电子邮件前缀为Email:并且不包含空格，您可以只取下Email:一个空格（或字符串结尾）前后的所有字符；

SELECT CASE WHEN CHARINDEX(' ', a.em) <> 0 
            THEN SUBSTRING(a.em, 1, CHARINDEX(' ', a.em) - 1)
            ELSE a.em END email
FROM (
  SELECT SUBSTRING(column1, CHARINDEX('Email: ', column1) + 7, LEN(column1)) em
  FROM mytable
) a

The subquery keeps anything after Email:and the outer query cuts everything trailing the next space (or end of string).

子查询保留后面Email:的任何内容，外部查询将删除下一个空格（或字符串结尾）后面的所有内容。

The query assumes that there is an Email:tag, if that's not guaranteed, you'll want to use a WHEREto make sure that only rows that have will be returned.

该查询假定有一个Email:标记，如果不能保证，您将需要使用 aWHERE来确保仅返回具有的行。

An SQLfiddle to test with.

一个用于测试的 SQLfiddle。

回答by Anthony R Gray

I'm making a few assumptions about your data, namely that the characters 'Name:' don't appear before the name and that each line includes the substring 'Terminate:'

我对您的数据做了一些假设，即字符“名称：”不会出现在名称之前，并且每一行都包含子字符串“终止：”

In SQL Server, use a combination of PATINDEX, CHARINDEX and SUBSTRING to parse the address from the string in each row. The cursor lets you loop through your table. This will print out all the e-mail addresses in your table. It needs formatting and if you want to search for a particular person's email, you will have to modify the select statement with a WHERE clause. I hope this helps:

在 SQL Server 中，使用 PATINDEX、CHARINDEX 和 SUBSTRING 的组合从每一行的字符串中解析地址。光标可让您循环浏览表格。这将打印出表格中的所有电子邮件地址。它需要格式化，如果您想搜索特定人的电子邮件，则必须使用 WHERE 子句修改 select 语句。我希望这有帮助：

declare @strung as nvarchar(255) ,@start as int ,@end as int ,@result as int ,@emailCursor Cursor

声明 @strung 为 nvarchar(255) ，@start 为 int ，@end 为 int ，@result 为 int ，@emailCursor Cursor

set @emailCursor = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR Select yourColumnName from yourTableName

set @emailCursor = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR 从 yourTableName 中选择 yourColumnName

OPEN @emailCursor   
FETCH NEXT FROM @emailCursor INTO @strung
WHILE @@FETCH_STATUS = 0    
    BEGIN                   
        set @start = (select charindex(':',@strung)+1);
        set @end = (SELECT PATINDEX('%Terminate:%', @strung)-1)
        set @result = @end-@start
        set @address = (select SUBSTRING(@strung, @start, @result ) AS eMailAddress)
        print @address
    FETCH NEXT FROM @emailCursor INTO @strung                       
    END

CLOSE @emailCursor              
DEALLOCATE @emailCursor

CHARINDEX returns the position of the first ':' character in your string (the one after EMAIL). I add one to that value to move you past the ':'

CHARINDEX 返回字符串中第一个 ':' 字符的位置（EMAIL 之后的那个）。我在该值上加 1 以使您跳过 ':'

PATINDEX returns the beginning position of the substring 'Terminate'

PATINDEX 返回子字符串 'Terminate' 的开始位置

SUBSTRING returns all the character between the starting position [CHARNINDEX(':', @strung)] and the space before 'Terminate' [PATINDEX('%Terminate:%', @strung)]

SUBSTRING 返回起始位置 [CHARNINDEX(':', @strung)] 和 'Terminate' 之前的空格之间的所有字符 [PATINDEX('%Terminate:%', @strung)]

回答by xQbert

http://sqlfiddle.com/#!6/5ce48/8/0

http://sqlfiddle.com/#!6/5ce48/8/0

select ltrim(rtrim(substring(column1,patindex(column1,':')+1, len(column1)-patindex(column1,':')-11))) as email
from t;

assumes Terminate: is consistent and first : denotes end of first characters to remove.

假设 Terminate: 是一致的， first : 表示要删除的第一个字符的结尾。

What it does:

它能做什么：

1. It uses SUBSTRING()to take out a string based on a specific starting character and end character.
2. To determine the start character we look for the first occurrence of : using PATINDEX()and add 2 1 for the space, 1 to move to the starting character. This gives us the starting potion for SUBSTRING()
3. To determine how many characters in the SUBSTRING()we take the LEN()of the entire string subtract from it the length of everything before the first ':' + 1 and the length of ' Terminate:' (11)

1. 它用于SUBSTRING()根据特定的起始字符和结束字符取出字符串。
2. 为了确定起始字符，我们查找第一次出现的 : usingPATINDEX()并添加 2 1 为空格，1 移动到起始字符。这为我们提供了起始药水SUBSTRING()
3. 为了确定SUBSTRING()我们LEN()从整个字符串中减去第一个 ':' + 1 之前的所有内容的长度和 ' Terminate:' 的长度，确定有多少个字符(11)

Again this HEAVILY assumes consistent formatting. If it's not ': space' and ' Terminate:' isn't 11 with a space, then this doesn't work.

同样，这非常假设格式一致。如果它不是 ':space' 并且 'Terminate:' 不是 11 带空格，那么这不起作用。

回答by Bhargav

This also works...

这也有效...

    declare @data varchar(100) = 'John Smith     Email: [email protected]
    Terminate:'

    select SUBSTRING(@data, PATINDEX('%Email%', @data)+7, PATINDEX('%com%', @data)- 
    PATINDEX('%Email%', @data)-4)