包含逗号分隔的字符串 [英] Contain in comma separate string

问题描述

你好，

我正在传递参数序列号.作为``18,8AB''逗号分隔的字符串.

我的桌子有序列号.数据
8018A7400BC3
8018A79B6433
001DD55C6FAB
HDE09B356634
4650C7453C90
3275D8AB544D
4345D8AB544C

因此，它应将结果返回为
8018A7400BC3
8018A79B6433
3275D8AB544D
4345D8AB544C

有什么主意吗?

谢谢

我尝试过的事情:

Hello,

I am passing parameter serial no. as ''18,8AB'' comma separate string.

my table has serial no. data
8018A7400BC3
8018A79B6433
001DD55C6FAB
HDE09B356634
4650C7453C90
3275D8AB544D
4345D8AB544C

So, it should return result as
8018A7400BC3
8018A79B6433
3275D8AB544D
4345D8AB544C

any idea?

Thanks

What I have tried:

tried with Contain and like query.

推荐答案

您必须先拆分字符串，然后使用这些部分来构造带有两个WHERE ... LIKE子句的SQL命令. br/>

You would have to split the string, and use the parts to construct an SQL command with two WHERE ... LIKE clauses

SELECT ... WHERE SerialNo LIKE '%18%' OR SerialNo LIKE '%8AB%'

然后您将不得不执行该SQL命令.

真是一团糟:SQL不能很好地处理字符串，因此这不是一种不错"的方式.这会执行类似的操作:在SQL IN中使用逗号分隔的值参数字符串条款 [ ^ ]，您可以对其进行修改，但是...

诚实地?我会用我的表示语言而不是SQL来做到这一点.

You will then have to EXEC that SQL command.

It''s a mess: SQL is not good at string handling, so this isn''t a "nice" way to do it. This does something similar: Using comma separated value parameter strings in SQL IN clauses[^] and you could modify that, but ...

Honestly? I''d do it in my presentation language, rather than SQL.

在SQL Server服务器端实现此目标的另一种方法是使用名为使用公用表表达式 [与common_table_expression(Transact-SQL)| Microsoft文档 [ OriginalGriff [

Another way to achieve that on SQL server server side is to use recursive queries named Common Table Expressions[^].
For further details, please see:
Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL) | Microsoft Docs[^]

I agree with OriginalGriff[^] (solution #1) that you should do that on presentation language (see example in c#).

Examples:

DECLARE @data TABLE (SerialNo VARCHAR(30));

INSERT INTO @data (SerialNo)
VALUES('8018A7400BC3'),
('8018A79B6433'),
('001DD55C6FAB'),
('HDE09B356634'),
('4650C7453C90'),
('3275D8AB544D'),
('4345D8AB544C');


DECLARE @find VARCHAR(30) = '18,8AB';

;WITH CTE AS 
(
	SELECT LEFT(@find, CHARINDEX(',', @find)-1) AS SerialPart, RIGHT(@find, LEN(@find) - CHARINDEX(',', @find)) AS Remainder
	WHERE CHARINDEX(',', @find)>0
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SerialPart, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM CTE
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS SerialPart, NULL AS Remainder
	FROM CTE
	WHERE CHARINDEX(',', Remainder)=0 
)
SELECT Orig.*
FROM @data AS Orig INNER JOIN CTE AS Parts ON Orig.SerialNo Like '%' + Parts.SerialPart + '%';

//create sample data
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("SerialNo", typeof(string)));
dt.Rows.Add(new object[]{"8018A7400BC3"});
dt.Rows.Add(new object[]{"8018A79B6433"});
dt.Rows.Add(new object[]{"001DD55C6FAB"});
dt.Rows.Add(new object[]{"HDE09B356634"});
dt.Rows.Add(new object[]{"4650C7453C90"});
dt.Rows.Add(new object[]{"3275D8AB544D"});
dt.Rows.Add(new object[]{"4345D8AB544C"});

string find = @"18,8AB";

var result = dt.AsEnumerable()
	.Where(x => find.Split(new string[]{","}, StringSplitOptions.RemoveEmptyEntries).Any(y=> x.Field<string>("SerialNo").Contains(y)))
	.ToList();
	
foreach(DataRow dr in result)
{
	Console.WriteLine("{0}", dr.Field<string>("SerialNo"));
}

祝你好运！

Good luck!

这篇关于包含逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！