Is it possible to delete part of string using regexp (or something else, may be something like CHARINDEX could help) in SQL query? I use MS SQL Server (2008 most likely).
Example:
I have strings like "[some useless info] Useful part of string" I want to delete parts with text in brackets if they are in line.
Use REPLACE
for example :
UPDATE authors SET city = replace(city, 'To Remove', 'With BLACK or Whatever')
WHERE city LIKE 'Salt%'; // with where condition
You can use the PATINDEX function. Its not a complete regular expression implementation but you can use it for simple things.
PATINDEX (Transact-SQL)> Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
OR You can use CLR to extend the SQL Server with a complete regular expression implementation.
SELECT * FROM temp where replace(replace(replace(url,'http://',''),'www.',''),'https://','')='"+url+"';
You can use STUFF to insert a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
For example, the code below, replaces the 5 with 666666:
DECLARE @Variable NVARCHAR(MAX) = '12345678910'
SELECT STUFF(@Variable, 5, 1, '666666')
Note, that the second argument is not a string, it is a position and you are able to calculate it position using CHARINDEX for example.
Here is your case:
DECLARE @Variable NVARCHAR(MAX) = '[some useless info] Useful part of string'
SELECT STUFF(
@Variable
,CHARINDEX('[', @Variable)
,LEN(SUBSTRING(@Variable, CHARINDEX('[', @Variable), CHARINDEX(']', @Variable) - LEN(SUBSTRING(@Variable, 0, CHARINDEX('[', @Variable)))))
,''
)
Finally helps REPLACE, SUBSTRING and PATINDEX.
REPLACE(t.badString, Substring(t.badString , Patindex('%[%' , t.badString)+1 , Patindex('%]%' , t.badString)), '').
Thanks to all.