How to append/concat a string after a comma on a comma-separated value?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

So you want to replace ', ' with ', [HI]'?

use replace(column, ', ', ', [HI]')

CodePudding user response：

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