SQL Server: any equivalent of strpos()?

问题:

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.

回答1:

User charindex:

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

Link

回答2:

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

回答3:

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)