'How to append/concat a string after a comma on a comma-separated value?
I have column which stores values separated by a comma. It's something like this:
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| breakfast_id | breakfast_english_menu |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Canned Orange Juice- Can of 5 to 7 oz, Oatmeal- ½ T, Almonds - (12 almonds), Milk with Coffee or Milk with Chocolate (1T.) (Only one) |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
and so I would like to add a string after each comma at the beginning of a 'new value'. It would be something like
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| breakfast_id | breakfast_english_menu |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Canned Orange Juice- Can of 5 to 7 oz, [HI]Oatmeal- ½ T, [HI]Almonds - (12 almonds), [HI]Milk with Coffee or Milk with Chocolate (1T.) (Only one) |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Any help or tip is welcomed.
Solution 1:[1]
As you have only a simple pattern use REPLACE
SELECT REPLACE('ned Orange Juice- Can of 5 to 7 oz, Oatmeal- ½ T, Almonds - (12 almonds), Milk with Coffee or Milk with Chocolate (1T.) (Only one)',', ',', [HI]')| REPLACE('ned Orange Juice- Can of 5 to 7 oz, Oatmeal- ½ T, Almonds - (12 almonds), Milk with Coffee or Milk with Chocolate (1T.) (Only one)',', ',', [HI]') | | :----------------------------------------------------------------------------------------------------------------------------------------------------------- | | ned Orange Juice- Can of 5 to 7 oz, [HI]Oatmeal- ½ T, [HI]Almonds - (12 almonds), [HI]Milk with Coffee or Milk with Chocolate (1T.) (Only one) |
db<>fiddle here
For more complicated patterns, you would use regular expressions to find and replace.
If need be, you can nest REPLACE comands one in another to repace more than one strintg
Solution 2:[2]
If you want to change the value in the DB you may use this query:
UPDATE [MyTable] SET
[breakfast_english_menu]=Replace([breakfast_english_menu],',',', [HI]')
Where [breakfast_id]=1
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | nbk |
| Solution 2 |