SQL Server: any equivalent of strpos()?

Question

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.

Answer 1

User charindex:

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

Link

Answer 2

Check out this blog post and this thread for some T-SQL "split" implementations.

Answer 3

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

Answer 4

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)