我如何在SQL中执行(in)函数 [英] How do I do (in) function in SQL
问题描述
|摆脱|
---------
| 5,6 |
我保存了这样的价值,我将返回任意一个值,例如
(从表中选择一些东西)名字在哪里摆脱5 // *我不知道在这里做什么*
我尝试了什么:
从表名中选择一些摆脱5
不要。
从不存储数据作为SQL中的逗号分隔列表 - 当你使用它时,它会让生活变得非常非常困难。
问题开始是因为列表必须是基于字符串的,所以一切都必须是字符串比较:
... WHERE Rid LIKE ' %5%' ...在这种情况下它匹配 15,16和51,73以及。
或者,每次你想要使用它时你必须解包它:将列中的逗号分隔数据转换为行以供选择 [ ^ ]这是非常混乱和低效的。
当谈到更新?这开始变得非常讨厌。你如何删除15和94:1,5,15,75,94,153,1515,9999并用23替换它们?什么是替换值是75,已经存在?
相反,重新设计你的数据库使用第二个表:
ExistingTable:
ID Desc
1 Hello
2 Banana
3 WorldNewTable:
ID ExistingTableID Rid
1 2 5
2 2 6
3 1 1
4 1 5
5 1 15
6 1 75
7 1 94然后使用简单的JOIN检索值,生活中的所有内容都更简单,更清晰。
创建用户定义将逗号分隔值转换为表格结果的函数。
我使用了 http:/ /www.devx.com/tips/Tip/20009 [ ^ ]
create function dbo.fun_Split(
@ String nvarchar ( 4000 ),
@分隔符 nvarchar ( 10 )
)
返回 @ ValueTable table ([Value] nvarchar ( 4000 ))
开始
声明 @ NextString nvarchar (< span class =code-digit> 4000 )
声明 @ Pos int
声明@ NextPos int @ NextString = substring( @ String , 1 , @ Pos - 1 )
声明 < span class =code-sdkkeyword> @ CommaCheck nvarchar ( 1 )
- 初始化
set @ NextString = ' '
set @ CommaCheck = right ( @ String , 1 )
- 检查尾随逗号,如果不存在,则INSERT
- - if(@CommaCheck<> @Delimiter)
set @ String = @ String + @Delimiter
- 获取第一个逗号的位置
set @ Pos = charindex( @Delimiter , @ String )
set @ NextPos = 1
- 循环,而字符串中仍有逗号
while ( @ pos <> 0 )
开始
set
insert into @ ValueTable ([Value])值( @ NextString )
set @ String = substring( @ String , @ pos + 1,len( @ String ))
set @ NextPos = @ Pos
set @ pos = charindex( @ Delimite r , @ String )
end
返回
结束
并使用以下
选择 ColumnName 来自 TableName 其中 ' 5' in ( select value 来自 dbo.fun_split(ColumnName,' ,'))
这不是推荐的方式,因为性能不好,你应该修改你的表模式到以更好的方式实现它......
我写过一篇提示,如何在SQL中查询逗号分隔值列 [ ^ ]检查它,它可以帮助您以多种方式查询CSV列。
| Rid |
---------
| 5,6 |
I have saved my vale like this ,I will return any of one value for example
(select something from table name where rid in 5 //*I dont kow what do in this*
What I have tried:
select something from table name where rid in 5
Don't.
Never store data as comma separated lists in SQL - it makes life really, really difficult when you come to use it.
The problems start because the list has to be string based, so everything has to be a string comparison:
... WHERE Rid LIKE '%5%' ...in which case it matches "15,16" and "51,73" as well.
Or, you have to "unpack" it every time you want to use it: Converting comma separated data in a column to rows for selection[^] which is horribly messy and inefficient.
And when it comes to updates? That starts to get really nasty. How do you remove "15" and "94" from this: "1,5,15,75,94,153,1515,9999" and replace them with "23"? And what is the replacement value was "75", which already exists?
Instead, redesign your DB to use a second table:
ExistingTable:
ID Desc 1 Hello 2 Banana 3 WorldNewTable:
ID ExistingTableID Rid 1 2 5 2 2 6 3 1 1 4 1 5 5 1 15 6 1 75 7 1 94You then retrieve the values with a simple JOIN and everything in life is a whole lot simpler and cleaner.
Create an user defined function for converting the Comma separated value to table result.
I have used the code from http://www.devx.com/tips/Tip/20009[^]
create function dbo.fun_Split( @String nvarchar (4000), @Delimiter nvarchar (10) ) returns @ValueTable table ([Value] nvarchar(4000)) begin declare @NextString nvarchar(4000) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1) --Initialize set @NextString = '' set @CommaCheck = right(@String,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) set @String = @String + @Delimiter --Get position of first Comma set @Pos = charindex(@Delimiter,@String) set @NextPos = 1 --Loop while there is still a comma in the String of levels while (@pos <> 0) begin set @NextString = substring(@String,1,@Pos - 1) insert into @ValueTable ( [Value]) Values (@NextString) set @String = substring(@String,@pos +1,len(@String)) set @NextPos = @Pos set @pos = charindex(@Delimiter,@String) end return end
and use the below
select ColumnName from TableName where '5' in ( select value from dbo.fun_split( ColumnName ,',' ) )
it is not a recommended way since the performance will not be good, you shall modify your table schema to achieve it in a better way...
I have wrote a Tip, How to Query Comma Seperated Value Column in SQL[^] Check it, It might help you in querying CSV column in multiple ways.
这篇关于我如何在SQL中执行(in)函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!