Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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.
–
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.