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.

score 37 · Accepted Answer · edited May 25 '23 at 13:35

37

User charindex:

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

Link

edited May 25 '23 at 13:35

Truisms Hounds

422
4
9

answered Jul 08 '09 at 19:01

Elzo Valugi

27,240
15
95
114

1

It's important to note that the order of arguments is switched here, leading to a lot of "String or binary data would be truncated" errors if you leave the needle and haystack in the same order as strpos. – Noumenon Oct 03 '17 at 21:48

score 12 · Answer 2 · answered Jul 08 '09 at 19:06

12

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

answered Jul 08 '09 at 19:06

Raj More

47,048
33
131
198

score 0 · Answer 3 · answered Jul 08 '09 at 19:58

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)

SQL Server: any equivalent of strpos()?

3 Answers3

Linked

Related