SQL Server：任何等价于 strpos() 的东西？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1099940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: any equivalent of strpos()?
提问by Kip
I'm dealing with an annoying database where one field contains what really should be stored two separate fields. So the column is stored something like "The first string~@~The second string", where "~@~" is the delimiter. (Again, I didn't design this, I'm just trying to fix it.)
我正在处理一个烦人的数据库，其中一个字段包含真正应该存储的两个单独字段的内容。因此该列存储的内容类似于“第一个字符串~@~第二个字符串”，其中“~@~”是分隔符。（同样，我没有设计这个，我只是想修复它。）
I want a query to move this into two columns, that would look something like this:
我想要一个查询将它移到两列中，看起来像这样：
UPDATE UserAttributes
SET str1 = SUBSTRING(Data, 1, STRPOS(Data, '~@~')),
str2 = SUBSTRING(Data, STRPOS(Data, '~@~')+3, LEN(Data)-(STRPOS(Data, '~@~')+3))
But I can't find that any equivalent to strpos exists.
但我找不到任何与 strpos 等效的存在。
回答by Elzo Valugi
回答by Raj More
The PatIndex function should give you the location of the pattern as a part of a string.
PatIndex 函数应该将模式的位置作为字符串的一部分提供给您。
PATINDEX ( '%pattern%' , expression )
回答by lstanczyk
If you need your data in columns here is what I use:
如果您需要列中的数据，我使用的是：
create FUNCTION [dbo].[fncTableFromCommaString] (@strList varchar(8000))
RETURNS @retTable Table (intValue int) AS
BEGIN
DECLARE @intPos tinyint
WHILE CHARINDEX(',',@strList) > 0
BEGIN
SET @intPos=CHARINDEX(',',@strList)
INSERT INTO @retTable (intValue) values (CONVERT(int, LEFT(@strList,@intPos-1)))
SET @strList = RIGHT(@strList, LEN(@strList)-@intPos)
END
IF LEN(@strList)>0
INSERT INTO @retTable (intValue) values (CONVERT(int, @strList))
RETURN
END
Just replace ',' in the function with your delimiter (or maybe even parametrize it)
只需将函数中的 ',' 替换为您的分隔符（或者甚至对其进行参数化）
