在逗号分隔的字符串中找到2end最后一个代码。 [英] Find the 2end last code in a comma seperated string.

问题描述

在我的sql语句中，我只想检查逗号分隔字符串中的2end最后一个代码。代码数量各不相同。



在这个例子中我只针对代码列表检查CX。


当instr（'AX，BX，CX，DX'，CODE）>时，


0



我的尝试：



i试图搜索Google解决方案

In my sql statement i only want to check the 2end last code in the comma seperated string. the amount of codes vary.

In this example i only what to check 'CX' against the code list.


when instr('AX,BX,CX,DX', CODE) > 0

What I have tried:

i have tried to search Google for a solution

推荐答案

我希望即使代码大小不同也会有效

I hope this will work even the code size vary

Declare @code varchar(20) ='AX,BX,CX,DX,XX'
;with cte as (
select 1 SNO,LEFT(@code,charindex(',',@code)-1)Code,RIGHT(@code,LEN(@code)-charindex(',',@code))RemCode
union all
select SNO+1,LEFT(RemCode,charindex(',',RemCode)-1),RIGHT(RemCode,LEN(RemCode)-charindex(',',RemCode))from cte where @code is Not null and charindex(',',RemCode)>0
)select Code from cte where SNO=(select max(SNO) from cte)

首先，SQL字符串处理最好，所以任何在SQL列中存储CSV数据的设计必须以极端的怀疑态度来看待 - 它可能设计得很糟糕，应该使用带有外键的单独表格，而不是原来的。



那里不是好的解决方案，但你可以使用它：将列中逗号分隔的数据转换为行以供选择 [ ^ ]一旦你将数据作为临时表，你可以从中选择你需要的任何项目。



但我认真考虑重新设计数据库以摆脱CSV数据 - 这只是它可能导致的许多令人讨厌的问题中的第一个。

First off, SQL string handling is poor at best, so any design which stores CSV data in SQL columns has to be viewed with extreme scepticism - it's probably badly designed and should use a separate table with a foreign key back to the original instead.

There is no "nice" solution, but you can use this: Converting comma separated data in a column to rows for selection[^] Once you have teh data as a temporary table, you can select any item you need from it.

But I'd seriously consider redesigning the DB to get rid of the CSV data - this is only the first of probably many nasty problems it will cause.

您要做的是利用字符串拆分功能。这样做的简单方法是使用多个查询;第一个将字符串转换为行，最后一个转换为特定行。



SQL Server 2016内置了此函数，名为 STRING_SPLIT

STRING_SPLIT（Transact-SQL）| Microsoft Docs [ ^ ]

其他版本的SQL可以安装类似的功能，有些比现在的本机功能更好。 SQL Server Central有很多关于这些测试的文档。



这是一个非常快速和丑陋的例程，具有你想要的基础知识。一旦理解，这应该被重构。



我将它保留为未定义，以便您可以看到所需操作的基础知识，这些操作可以强化 OriginalGriff 断言字符串函数不是SQL强项。

使用C＃或任何语言并将其转换为数组会好得多，那么你可以通过索引获取任何值

What you are going to want to do is to utilize a string splitting function. The easy way to do this is going to use multiple queries; the first to convert the string to rows, and the last to get the specific row.

SQL Server 2016 has this function built in, called STRING_SPLIT
STRING_SPLIT (Transact-SQL) | Microsoft Docs[^]
Other versions of SQL can have similar functions installed, some are better than the now native function. SQL Server Central has quite a few and documentation on testing of these.

This is a very quick and ugly routine that has the basics of what you want. Once understood this should be refactored.

I am leaving it unrefined so that you can see the basics of the operations that are needed which reinforces OriginalGriff's assertion that string functions just aren't SQLs forte.
You would be much better off working in C# or whatever language and converting this to an array, then you can just grab whatever value by index

DECLARE @ElementCount INT =0
DECLARE @CSV NVARCHAR(1000) = 'AX,BX,CX,DX'
DECLARE @DesiredValue NVARCHAR(100)

DECLARE @SplitTable TABLE(idx INT IDENTITY(1,1) NOT NULL, Element NVARCHAR(100))

INSERT @SplitTable SELECT val FROM STRING_SPLIT(@CSV, ',')
SET @ElementCount = @@RowCount

SELECT @DesiredValue = val FROM @SplitTable WHERE idx = @ElementCount -1 

PRINT @DesiredValue

这篇关于在逗号分隔的字符串中找到2end最后一个代码。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！