Multiple Replace Statements in a single string in SQL

Question

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: Economics
~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

Quickest and easiest way would be to just nest the replace statements so you end up with 5 nested replaces. SQL Server doesn't support regular expressions — Martin Smith
– Martin Smith, Commented Sep 12, 2022 at 19:00

Rajat · Accepted Answer · 2022-09-15 17:55:21Z

1

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

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

edited Sep 15, 2022 at 17:55

answered Sep 13, 2022 at 22:05

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

YelizavetaYR Over a year ago

I would still have to list all the column names in the in portion of the code with this logic?

Rajat Over a year ago

@YelizavetaYR Sorry I misread your comment earlier. The values inside in are the values in your column that you want to clean up. The where clause filters for those groups where values in notes start with 'Phone Number', 'School Name' and so on.

Hogan · Accepted Answer · 2022-09-13 22:07:39Z

0

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

edited Sep 13, 2022 at 22:07

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

answered Sep 13, 2022 at 20:31

Cam

112 bronze badges

1 Comment

YelizavetaYR Over a year ago

Hello, the only issue is that there are paragraphs in some of the questions, those line breaks we want to avoid. We only want to update the line breaks with the actual questions in them.

Collectives™ on Stack Overflow

Multiple Replace Statements in a single string in SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related