-2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohammed Bazbazat
  • 3
  • 1
  • Removed the `plsql` tag - that would be for **Oracle** PL/SQL - not SQL Server – marc_s May 22 '20 at 12:52
  • You mean ? : update mytable set myfield = replace(myfield, '(123456)', '') – Marc Guillot May 22 '20 at 12:55
  • item a (1234) for men item b (986) for kids I need these items like this item a for men item b for men – Mohammed Bazbazat May 22 '20 at 13:01
  • If you have differing strings (some with multiple replacements required) you should be detailing that in your question, @MohammedBazbazat . That isn't explained in your question at all. Seems like what you are after is Regex replacement. SQL Server doesn't natively support regex, so you'll be better off looking at clr functions in my opinion. – Thom A May 22 '20 at 13:07
  • You ask a question, you get an answerh, then you come with "oh, i have diferent strings, my problem is acutally different". Sorry, we can not help you if you do not sit down and organize your thoughts. – TomTom May 22 '20 at 13:26

1 Answers1

0

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,'')
Thiyagu
  • 1,260
  • 1
  • 5
  • 14