Search code examples
sql-serverselectreplacereplaceall

SQL Server select statment

I have over 2000 rows, and I want to replace the text in these formats:

lorem ipsum (123456) lorem ipsum lorem ipsum

What I need is to remove the (123456) from all products for all 2000+ rows.

Solution

  • You can use STUFF function to achieve this:

    DECLARE @S VARCHAR(MAX)='lorem ipsum (123456) lorem ipsum lorem ipsum'

SELECT STUFF(@S,PATINDEX('%[()0-9]%', @S),CHARINDEX(')', @S, PATINDEX('% [()0-9]%', @S))-PATINDEX('%[()0-9]%', @S)+1,'')