SQL Server: any equivalent of strpos()?

iq3niunx 于 2023-05-28 发布在 SQL Server

关注(0)|答案(3)|浏览(108)

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.

sql-server

来源：https://stackoverflow.com/questions/1099940/sql-server-any-equivalent-of-strpos

3条答案

按热度按时间

b4wnujal1#

User charindex:

Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6

Link

赞(0）回复(0）举报 2023-05-28

9nvpjoqh2#

The PatIndex function should give you the location of the pattern as a part of a string.

PATINDEX ( '%pattern%' , expression )

http://msdn.microsoft.com/en-us/library/ms188395.aspx

赞(0）回复(0）举报 2023-05-28

gojuced73#

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)

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server: any equivalent of strpos()?

3条答案

相关问题

热门标签

最新问答