Multiple Replace Statements in a single string in SQL-CodePudding

a sample of it looks like this in my table with new line breaks and all. This is just one sample of the questions, I have about 15 of these groups of questions with different question/answers combos. All follow the same format question: answer <new line break>

Phone Number: 1234567890
School Name: UCLA
Major: Economics
Graduation Date: 06/2012
Birthday: 01/01/1990

I need to run some update statements to make this data look like this.

~Phone Number: 1234567890
~School Name: UCLA
~Major: Bassoon
~Graduation Date: 06/2012
~Birthday: 01/01/1990

Now this example has 5 lines. However there are fields with 10 lines of questions and answers.

Individually these are pretty strait forward replace statements and my sql looks like this.

select 
replace(notes, 'Phone Number: ', '~Phone Number: '),
replace(notes, 'School Name: ', '~School Name: '),
replace(notes, 'Major:', '~Major: '),
replace(notes, 'Graduation Date: ', '~Graduation Date: '),
replace(notes, 'Birthday: ', '~Birthday: ')
from notes 
where ...

I can run update statements piecemeal, but I would like to run one update statement and update each group (this is one group here) with one update statement.

Thank you

CodePudding user response：

This makes your query non-sargeable, but if performance is not of top concern, you could do

update my_table
set my_col=concat('~',my_col)
where left(my_col,charindex(':',mycol)-1) in ('Phone Number','School Name' and so on)

CodePudding user response：

If all you are doing is adding a character (~) to the beginning of each line, could you replace line breaks with a line break plus '~'?

UPDATE [tablename]
  SET [columnname] = replace([columnname], 
                             CHAR(10), 
                             CONCAT(CHAR(10), 
                             '~'))

See: https://www.sqlshack.com/sql-carriage-return-or-tab-in-sql-server-string/ for more info on line breaks and carriage returns in SQL Server