views:

1980

answers:

4

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.

+6  A: 

User charindex:

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

Link

Elzo Valugi
thanks!........
Kip
no problem, but you have to remember that string parsing in SQL is resource intensive so use with care!
Elzo Valugi
A: 

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

JP Alioto
+1  A: 

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

Raj More
A: 

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)

lstanczyk