SQL在表中选择行，如果给定条件是用分隔符＆＃x27 ;;＆＃x27;在基因之间

发布于 2025-01-28 10:01:02 字数 1426 浏览 4 评论 0原文

如果列[to]具有邮件abc@邮件，我正在尝试从表邮件中获取行。

简单的解决方案将是从邮件中选择 * [to] ='abc@mail'

，但[to To]列具有诸如123@mail; abc@mail; aabc@mail eq由分号在哪里发送的多封电子邮件，

我知道我可以做从邮件中选择 *，例如'％abc@mail％'，但如果给定的邮件是一个另一封邮件的子字符串。我想到了一个拆分字符串解决方案，

我具有这样的split_string函数，

CREATE FUNCTION [dbo].[split_string]
(
@string_value NVARCHAR(MAX),
@delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
DECLARE @start_position INT,
        @ending_position INT
SELECT @start_position = 1,
        @ending_position = CHARINDEX(@delimiter_character, @string_value)
WHILE @start_position < LEN(@string_value) + 1
        BEGIN
    IF @ending_position = 0 
       SET @ending_position = LEN(@string_value) + 1
    INSERT INTO @result_set (splited_data) 
    VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
    SET @start_position = @ending_position + 1
    SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
END
RETURN
END

该函数将返回列中单个数据的拆分字符串，并且该函数正常工作。

查询

Select * 
from Mails 
where 'abc@mail' in (
   Select * 
   from dbo.split_string((SELECT [To] FROM Mails) , ';')
)

我尝试执行引发错误的

：
子查询返回超过1个值。当子查询collats =，！=，＆lt;，＆lt; =，＆gt;，＆gt; =或当子查询用作子查询时表达式。

我需要从这里开始的帮助。我正在使用Microsoft SQL Server 2014。

原文

I am trying to get rows from the table Mails if the column [To] has the mail abc@mail.

The simple solution would be Select * from Mails where [To] = 'abc@mail'

But the thing is [To] column has data like 123@mail;abc@mail;aabc@mail etc separated by semicolons
where To is multiple emails sent

I know I could do something like Select * from Mails where [To] like '%abc@mail%' but that won't solve the problem if the given mail is a substring of another mail. I thought of a split string solution

I have a split_string function like this,

CREATE FUNCTION [dbo].[split_string]
(
@string_value NVARCHAR(MAX),
@delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
DECLARE @start_position INT,
        @ending_position INT
SELECT @start_position = 1,
        @ending_position = CHARINDEX(@delimiter_character, @string_value)
WHILE @start_position < LEN(@string_value) + 1
        BEGIN
    IF @ending_position = 0 
       SET @ending_position = LEN(@string_value) + 1
    INSERT INTO @result_set (splited_data) 
    VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
    SET @start_position = @ending_position + 1
    SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
END
RETURN
END

which would return splitted string of a single data in a column and the function is working fine.

I tried executing the query

Select * 
from Mails 
where 'abc@mail' in (
   Select * 
   from dbo.split_string((SELECT [To] FROM Mails) , ';')
)

which is throwing the error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

I need help proceeding from here. I am using Microsoft SQL Server 2014.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

素罗衫 2025-02-04 10:01:02

您不能将多行子查询作为参数传递给dbo.split_string函数。尝试将您的表函数连接到邮件表：

SELECT DISTINCT ms.*
    FROM Mails AS ms
    CROSS APPLY dbo.split_string(ms.[To], ';') AS s
    WHERE s.splited_data LIKE 'abc@mail'

如果您可以在2016年（13.x）上延新SQL Server，则可以使用内置string_string_split表函数而不是自定义dbo.split_string。

另外，您可以通过蛮力实现目标，并将比较分解为简单的术语，例如：

SELECT * 
    FROM Mails 
    WHERE 
        [To] LIKE 'abc@mail'
        OR [To] LIKE '%;abc@mail;%'
        OR [To] LIKE 'abc@mail;%' 
        OR [To] LIKE '%;abc@mail'

这可能不是最好的方法，但它非常简单，不需要拆分功能。

You can't pass a multi-row subquery as an argument to the dbo.split_string function. Try to join your table function to the Mails table:

SELECT DISTINCT ms.*
    FROM Mails AS ms
    CROSS APPLY dbo.split_string(ms.[To], ';') AS s
    WHERE s.splited_data LIKE 'abc@mail'

If you can ubgrade your SQL Server up to 2016 (13.x), you can use built-in STRING_SPLIT table function instead of custom dbo.split_string.

Alternatively, you can achieve your goal with brute force and break down the comparison into simple terms such as follows:

SELECT * 
    FROM Mails 
    WHERE 
        [To] LIKE 'abc@mail'
        OR [To] LIKE '%;abc@mail;%'
        OR [To] LIKE 'abc@mail;%' 
        OR [To] LIKE '%;abc@mail'

It might not be the best way, but it's pretty simple and doesn't require a split function.

回复收藏 0 原文

一场信仰旅途 2025-02-04 10:01:02

tl; dr;
这是您想要的查询，

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data = 'abc@mail'
)

我建议您采用分裂方法。任何角色查找都必须考虑半彩色的可变性，而将其分开将处理半彩的位置的歧义，然后您可以进行直接的平等检查。如果您想更进一步，并寻找其他[到]地址，则可以添加这样的子句，而SQL Server不必做更多的工作，并且获得了相同的结果。

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data IN ('abc@mail', 'def@mail')
)

我的答案与@kitta的答案相似，因为我们将数据分开了，并且@kitta对In子句是正确的，但是尽管它们的答案将起作用，但您的答案将需要您将数据重新分组在一起以获得单一的答案。使用该条款将为您绕过所有这些条款，只为您提供原始表中的数据。话虽这么说，如果他们的答案也对您有效，请标记@kitta作为答案。

测试安装

DROP TABLE Mails
GO
CREATE TABLE Mails
([To] VARCHAR(3000))

INSERT INTO dbo.Mails
(
    [To]
)
VALUES
('123@mail;abc@mail;aabc@mail')
,('[email protected]')
,('[email protected];[email protected]')
,('aabc@mail;ewrkljwe@mail')
,('ewrkljwe@mail')

GO
DROP FUNCTION [split_string]
GO
CREATE FUNCTION [dbo].[split_string]
(
@string_value NVARCHAR(MAX),
@delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
DECLARE @start_position INT,
        @ending_position INT
SELECT @start_position = 1,
        @ending_position = CHARINDEX(@delimiter_character, @string_value)
WHILE @start_position < LEN(@string_value) + 1
        BEGIN
    IF @ending_position = 0 
       SET @ending_position = LEN(@string_value) + 1
    INSERT INTO @result_set (splited_data) 
    VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
    SET @start_position = @ending_position + 1
    SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
END
RETURN
END
GO


SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data = 'abc@mail'
)

这是我使用的

TL;DR;
Here is the query that you want

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data = 'abc@mail'
)

I recommend the splitting approach. Any character lookup will have to account the variability of the semi-colons, whereas splitting it out will handle the ambiguity of where the semi-colons are, and then you can do a direct equality check. If you wanted to take it a step further and look for additional [To] addresses you can just add an IN clause like this and SQL Server doesn't have to do much more work and you get the same results.

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data IN ('abc@mail', 'def@mail')
)

My answer is fairly similar to @Kitta answer in that we split the data out, and @Kitta is correct about the IN clause, but while their answer will work it will require you grouping your data back together to get a singular answer. Using the EXISTS clause will bypass all of that for you and only give you the data from the original table. That being said, please mark @Kitta as the answer if their answer works just as well for you.

Here is the test setup that I used

DROP TABLE Mails
GO
CREATE TABLE Mails
([To] VARCHAR(3000))

INSERT INTO dbo.Mails
(
    [To]
)
VALUES
('123@mail;abc@mail;aabc@mail')
,('[email protected]')
,('[email protected];[email protected]')
,('aabc@mail;ewrkljwe@mail')
,('ewrkljwe@mail')

GO
DROP FUNCTION [split_string]
GO
CREATE FUNCTION [dbo].[split_string]
(
@string_value NVARCHAR(MAX),
@delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
DECLARE @start_position INT,
        @ending_position INT
SELECT @start_position = 1,
        @ending_position = CHARINDEX(@delimiter_character, @string_value)
WHILE @start_position < LEN(@string_value) + 1
        BEGIN
    IF @ending_position = 0 
       SET @ending_position = LEN(@string_value) + 1
    INSERT INTO @result_set (splited_data) 
    VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
    SET @start_position = @ending_position + 1
    SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
END
RETURN
END
GO


SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data = 'abc@mail'
)

and it returns the correct row of '123@mail;abc@mail;aabc@mail'

回复收藏 0 原文

假装爱人 2025-02-04 10:01:02

您可以使用charindex（）函数。

select * from Mails where CHARINDEX('[email protected]', To) > 0

CharIndex函数检查给定字符串中的子字符串，并在找到该值的情况下返回大于零的值。在这里，您的字符串要搜索（子字符串）将为' [emagy＆nbsp; emagy＆nbsp;从哪里搜索的主字符串将是“到”列。

You can use CHARINDEX() function.

select * from Mails where CHARINDEX('[email protected]', To) > 0

The CHARINDEX function checks for a substring within a given string and returns a value greater than zero if it is found. Here your string to search (substring) would be '[email protected]' and the main string from where to search would be the "To" column.

More information about CHARINDEX() function can be found at below link
https://www.techonthenet.com/sql_server/functions/charindex.php#:~:text=SQL%20Server%3A%20CHARINDEX%20Function%201%20Description.%20In%20SQL,use%20the%20CHARINDEX%20function%20in%20SQL%20Server%20%28Transact-SQL%29.

回复收藏 0 原文

~没有更多了~