Regex in Replace?

Question

Regex in Replace?

ken.trock

SSCertifiable

Points: 5147
More actions
September 25, 2018 at 12:27 pm

#377734

Hello. I'm trying to turn something like '{1,2,3,4,5}' into '1,2,3,4,5'. I can't be certain that the curly braces will always start and end the input string. I thought
SELECT REPLACE('{1,2,3,4,5}', '[{}]', '')
might work but no. Apparently this type of thing has generated confusion in SQL 2012.
Thanks,
Ken

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

ken.trock - Tuesday, September 25, 2018 12:27 PM
Hello. I'm trying to turn something like '{1,2,3,4,5}' into '1,2,3,4,5'. I can't be certain that the curly braces will always start and end the input string. I thought
SELECT REPLACE('{1,2,3,4,5}', '[{}]', '')
might work but no. Apparently this type of thing has generated confusion in SQL 2012.
Thanks,
Ken

REPLACE() does not support regular expressions. You need to have nested REPLACEs, one to replace '{' and one to replace '}'.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246977 More actions · Answer 2

ken.trock - Tuesday, September 25, 2018 12:27 PM
Hello. I'm trying to turn something like '{1,2,3,4,5}' into '1,2,3,4,5'. I can't be certain that the curly braces will always start and end the input string. I thought
SELECT REPLACE('{1,2,3,4,5}', '[{}]', '')
might work but no. Apparently this type of thing has generated confusion in SQL 2012.
Thanks,
Ken

If you are just removing all braces wherever and if they are present, Drew's suggestion will work best. If your requirement is more complex than that, please clarify.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ken.trock SSCertifiable Points: 5147 More actions · Answer 3

Phil Parkin - Tuesday, September 25, 2018 2:21 PM
ken.trock - Tuesday, September 25, 2018 12:27 PM
Hello. I'm trying to turn something like '{1,2,3,4,5}' into '1,2,3,4,5'. I can't be certain that the curly braces will always start and end the input string. I thought
SELECT REPLACE('{1,2,3,4,5}', '[{}]', '')
might work but no. Apparently this type of thing has generated confusion in SQL 2012.
Thanks,
Ken
If you are just removing all braces wherever and if they are present, Drew's suggestion will work best. If your requirement is more complex than that, please clarify.

No, that's fine, thanks. It looks like PATINDEX doesn't support Regex either. I've seen some folks doing CLR work to accomplish this.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 4

I agree. For true Regex work in SQL Server, CLRs are the way to go.
PATINDEX supports a cut-down REGEX-like syntax (here's a link demonstrating some examples).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Regex isn't necessary if all that needs doing is to replace every occurrence of curly braces. It really is as simple as:
REPLACE(REPLACE(YourColumnName, '{', ''), '}', '')

That's just a nested set of REPLACE functions.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

ken.trock - Tuesday, September 25, 2018 2:39 PM
Phil Parkin - Tuesday, September 25, 2018 2:21 PM
ken.trock - Tuesday, September 25, 2018 12:27 PM
Hello. I'm trying to turn something like '{1,2,3,4,5}' into '1,2,3,4,5'. I can't be certain that the curly braces will always start and end the input string. I thought
SELECT REPLACE('{1,2,3,4,5}', '[{}]', '')
might work but no. Apparently this type of thing has generated confusion in SQL 2012.
Thanks,
Ken
If you are just removing all braces wherever and if they are present, Drew's suggestion will work best. If your requirement is more complex than that, please clarify.
No, that's fine, thanks. It looks like PATINDEX doesn't support Regex either. I've seen some folks doing CLR work to accomplish this.

MS SQL Server is one of the few SQL languages without non-CLR support for regular expressions (regex). I have since discovered that you can get by just fine without regex. As a matter of fact, the alternatives to regex I have been forced to come up with generally perform much better than it's regex equivalent.

Yes - if all you need to do is remove squiggly brackets then Drew and sgmunson have told you all you need to know. Let's say however, you needed to remove all non-alphanumeric characters except for commas and spaces. You could leverage PatExclude or PatReplace for this. Example:

DECLARE @string VARCHAR(1000) = 'Some text {1,2,3,4,5} {apples, pears}... blah, blah!!!';

SELECT newString = pr.NewString FROM dbo.patReplace(@string, '[^0-9 a-zA-Z,]', '') AS pr

This would return: Some text 1,2,3,4,5 apples, pears blah, blah

If you needed to remove brackets only when they are surrounding a comma separated list of numbers you could use PatternSplitCM (link in my signature) like this:

DECLARE @string VARCHAR(1000) = 'Some text {1,2,3,4,5} {apples, pears}... blah, blah!!!';

SELECT newString = ( SELECT IIF(ps.item LIKE '%{[0-9],%}' AND ps.item NOT LIKE '%[^{}0-9,]%', REPLACE(REPLACE(ps.Item,'}',''),'{',''), ps.item) FROM dbo.PatternSplitCM(@string,'[{}0-9,]') AS ps ORDER BY ps.ItemNumber FOR XML PATH(''), TYPE ).value('text()[1]', 'VARCHAR(8000)');

Returns: Some text 1,2,3,4,5 {apples, pears}... blah, blah!!!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001