Menu
Alright 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 all the occurrence with more that one XXX prefix to just 1. For e.g id 2 should have Name=XXX - New2. How would go about achieving this ? Even a query basically to check for a prefix and replace it with the name would work I think and then I can add the prefix again ? I mean set all the records just to say New,New2..so on...and then I can add XXX prefix to it ?
356
SQL Server REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.
To remove this prefix of 'MR. ', we need to use the keywords UPDATE, SET, RIGHT, LEN, and WHERE. The following command updates the entry of the column starting with 'MR. ' with a substring of the name extracted using RIGHT keyword.
On the Edit menu, point to Find and Replace, and then click Quick Replace to open the dialog box with both find options and replace options. Toolbar buttons and shortcut keys are also available to open the Find and Replace dialog box.
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
You can remove all instances of 'XXX - ' with the string replace function.
UPDATE tableName
SET Name = Replace(Name, 'XXX - ', '')
Alternately, to keep a single instance you could use:
UPDATE tableName
SET Name = 'XXX - ' + Replace(Name, 'XXX - ', '')
WHERE CHARINDEX('XXX - ', Name) > 0
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
