Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                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 3, 2017 at 21:48
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
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)
    IF LEN(@strList)>0 
        INSERT INTO @retTable (intValue) values (CONVERT(int, @strList))
    RETURN
Just replace ',' in the function with your delimiter (or maybe even parametrize it) 
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.