view=sql-server-ver15 https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 */ DECLARE @Number VARCHAR(4000); SET @string = @string + @separator; WHILE CHARINDEX(@separator, @string) > 0 BEGIN SET @Number = SUBSTRING(@string, 0, CHARINDEX(@separator, @string)); ...
下面是一个示例代码: CREATEFUNCTIONdbo.SplitString(@StringNVARCHAR(MAX),@DelimiterCHAR(1))RETURNSTABLEASRETURN(WITHSplit(stpos,endpos)AS(SELECT0ASstpos,CHARINDEX(@Delimiter,@String)ASendposUNIONALLSELECTendpos+1,CHARINDEX(@Delimiter,@String,endpos+1)FROMSplitWHEREendpos>0)SELECTSUBSTRING(@String,stpos,...
CREATEFUNCTIONdbo.SplitString(@StringNVARCHAR(MAX),-- 输入的字符串@DelimiterCHAR(1)-- 分隔符)RETURNS@ResultTABLE(Item NVARCHAR(MAX))ASBEGINDECLARE@IndexINTDECLARE@SliceNVARCHAR(MAX)SELECT@Index=1WHILE@Index!=0BEGINSET@Index=CHARINDEX(@Delimiter,@String)IF@Index!=0SET@Slice=LEFT(@String,@Index-1...
SQL SELECTProductId,Name, TagsFROMProductWHEREEXISTS(SELECT*FROMSTRING_SPLIT(Tags,',')WHEREvalueIN('clothing','road')); E. 依據值清單來尋找資料列 開發人員必須建立依據識別碼清單尋找發行項的查詢。 他們可以使用下列查詢: SQL SELECTProductId,Name, TagsFROMProductJOINSTRING_SPLIT('1,2,3',',')ON...
有些应用中,参数的值是不确定的,是用逗号拼接后,传入sql中,这个时候string_split特别有用,可以和in搭配使用。 SELECT t.id, t.name, t.description FROM test t WHERE t.id IN (SELECT CAST(v.value AS INT) AS id FROM STRING_SPLIT('1,2,3', ',') v) ...
update a set a.[Order]=t.[Id] from [dbo].[表] as a join [dbo].SplitString('1,2,3', ',', 1) as t on a.[Id]=t.[Value] 具体的应用请根据自己的情况来吧:) 方法二: 代码语言:javascript 代码运行次数:0 运行 AI代码解释
有时我们要用到批量操作时都会对字符串进行拆分,可是SQL Server中却没有自带Split函数,所以要自己来实现了。没什么好说的,需要的朋友直接拿去用吧 SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO /* by kudychen 2011-9-28 */ CREATEfunction[dbo].[SplitString] ...
SQL SELECTProductId,Name, TagsFROMProductWHEREEXISTS(SELECT*FROMSTRING_SPLIT(Tags,',')WHEREvalueIN('clothing','road')); E. 按一系列值查找行 开发人员必须创建一个按 ID 列表查找文章的查询。 可以使用以下查询: SQL SELECTProductId,Name, TagsFROMProductJOINSTRING_SPLIT('1,2,3',',')ONvalue= Pr...
SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric STRING_SPLITis a table-valued function that splits a string into rows of substrings, based on a specified separator char...
SQL Kopiuj SELECT ProductId, Name, Tags FROM Product JOIN STRING_SPLIT('1,2,3',',') ON value = ProductId; The preceding STRING_SPLIT usage is a replacement for a common antipattern. Such an antipattern can involve the creation of a dynamic SQL string in the application layer or in...