Menu
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.
502
SQL Server CHARINDEX() Function The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search.
Searching from the start of a string expression. This example returns the first location of the string is in string This is a string , starting from position 1 (the first character) of This is a string . SELECT CHARINDEX('is', 'This is a string'); Here is the result set.
SUBSTRING() Function in SQL Server The SUBSTRING() function extracts a substring starting from a position in an input string with a given length. In the case of substring, you need an input string and need to mention the starting point and the total length of the string.
The LIKE predicate operator can be used to find a substring into a string or content. The LIKE operator combined with % and _ (underscore) is used to look for one more characters and a single character respectively. You can use % operator to find a sub-string.
User charindex:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6
Link
130
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
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
