Sql query to find and replace specific prefix strings?

Question

Ok, I have something like this (I messed it up ...) -

Id Name City 1 XXX - New Plano 2 XXX - XXX - New1 Dallas 3 XXX - XXX - XXX - New2 Sacramento 4 XXX - New3 Houston 5 XXX - XXX - New4 Austin 

So, I want to replace the entire occurrence with more than one XXX prefix equal to 1. For example, id 2 should have Name = XXX - New2 . How to achieve this? Even the question is basically checking the prefix and replacing it with the name will work, I think, and then can I add the prefix again? I mean to set all the records just to say New , New2 .. so on ... and then I can add the XXX prefix to it?

Answer 1

You can remove all instances of "XXX -" using the replace string.

 UPDATE tableName SET Name = Replace(Name, 'XXX - ', '') 

Alternatively, to save one instance that you could use:

 UPDATE tableName SET Name = 'XXX - ' + Replace(Name, 'XXX - ', '') WHERE CHARINDEX('XXX - ', Name) > 0 

Answer 2

You want to strip occurrences from 2 to n of the string " XXX - ". This involves storing data at the end:

 SELECT 'Starting' --also sets @@ROWCOUNT WHILE @@ROWCOUNT <> 0 UPDATE Bollixedtable SET name = STUFF (name, 7, 6, '') WHERE SUBSTRING (name, 7, 6) = 'XXX - ' 

Otherwise, it will delete XXX and discard your data based on the template XXX - New(id-1) without filter if you want to start again

  UPDATE Bollixedtable SET name = 'XXX - New' + CAST(id-1 AS varchar) 

Edit: Updated for clarity.

Answer 3

 Update Table Set Name = Replace( Replace( Replace( Name, ' - XXX - ', ' - ') , ' - XXX - ', ' - ') , ' - XXX - ', ' - ') 

This solution should handle most of the options. For example, it will process all the way to:

XXX - XXX - XXX - XXX - XXX - XXX - XXX - XXX - New2 .

If you need to process it further, you can simply add another Replace call.

Answer 4

Depends on the data and their uniformity. If all your data falls into the formats that you have in your question, then the simplest will be:

 SELECT ID, 'XXX - ' + SUBSTRING(NAME, CHARINDEX('NEW',NAME),LEN(Name)-CHARINDEX('NEW',NAME)) AS Name, CITY FROM FOO 

Otherwise, it can become much more complicated. Tell us more about the uniformity of your data and what format you will use, i.e. Do you have XXXX - XXXX - XXXX - XXXX - ad infintum ?

Answer 5

Assuming you just need to remove the line of all the parts before the last " - ", and then add the prefix again, you can use this:

 UPDATE tblWhatever SET Name = 'NewPrefix - ' + RIGHT(Name, CHARINDEX(' - ',REVERSE(Name))-1) 

It just basically searches for the last occurrence of a “ - ”, and then uses the right() function to get the last part of the string. He then adds a prefix to this.

This, in fact, is what you described in the last part of your question.

Answer 6

 update myTable set name = rtrim(ltrim(replace(name, 'XXX - XXX', 'XXX - '))) update myTable set name = rtrim(ltrim(substring(name, charindex(name, '-'), len(name)))) where name like '%-%' 

Answer 7

 declare @loop bit set @loop = 1 while @loop begin update table set name = right(name,len(name)-6) where left(name,12) = 'xxx - xxx - ' set @loop = @@rowcount end