Find replace using regular expressions

Question

Post reply

Find replace using regular expressions

masterelaichi

SSC-Addicted

Points: 444
More actions
August 27, 2022 at 12:37 am

#4081085
I am trying to find an easy way to vertically arrange numerous columns in a select statement using Find/Replace on regular expressions. While I am able to do it in such a way there is one single column of columns what I am not able to figure out is a a regular expression to create two columns of columns
Might be better with the below example -
SELECT [column 1], [Column A], [Column_2], [ColumnB], column3, column4, column5
I highlight only the columns and select regular expression on the Find/Replace window and replace all occurrences of a space using carriage return ,i.e, I replace ", " with ",\r". This returns a single column
What I am trying find is a regular expression that will create column pairs such below
```
SELECT [column 1], [Column A]

,[Column_2], [ColumnB]

,column3, column4

, column5
```
There is no purpose for this other than cleaning up the code visually and doing it in an efficient way when there are numerous columns on a single line. The regular expression should work on any character combination
- This topic was modified 2 years, 2 months ago by masterelaichi.
September 17, 2022 at 7:46 pm

This was removed by the editor as SPAM
November 5, 2022 at 3:52 pm

This was removed by the editor as SPAM
November 5, 2022 at 5:46 pm

This was removed by the editor as SPAM
November 5, 2022 at 5:47 pm

This was removed by the editor as SPAM
November 5, 2022 at 5:49 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

I've done some things using the "RegEx" button (which is a serious misnomer of it's own) on Search'n'Replace in SSMS but nothing like this. I can see other uses for doing things like this. Think of this as another "bump" for your good question and I seriously hope someone know of or can figure out a way to do this in SSMS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

There’s nothing (that I’m aware of) that will do exactly that. If you have RedGate SQL Prompt, you can get close(ish) using a custom style format.

This reply was modified 2 years, 2 months ago by Jason A. Long.

drew.allen SSC Guru Points: 76985 More actions · Answer 4

I believe that this does close to what you are looking for. It can probably be massaged to do exactly what you want. The big problem is that the trailing , is included in the pattern, and there isn't an easy way to insert a \r within the pattern, so you would need to do this in two steps or keep the trailing comma instead of converting to a leading comma.

Search string: (([^,]+, ){2}

Replacement string: $0\r

The {2} searches for exactly two consecutive matches of the specified pattern. If you use $1 instead of $0 in the replacement string, it will use only the second match for the replacement.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

I would use an SSMS add-in like ApexSQL Refactor.

There is a free version of it here https://en.freedownloadmanager.org/Windows-PC/ApexSQL-Refactor-FREE.html

I think you only get a trial period with the current version from the Apex site so better to use the download above.

Or if you have a budget and don't mind paying for something I would get Redgate SQL Prompt.

masterelaichi SSC-Addicted Points: 444 More actions · Answer 6

drew.allen wrote:

I believe that this does close to what you are looking for. It can probably be massaged to do exactly what you want. The big problem is that the trailing , is included in the pattern, and there isn't an easy way to insert a \r within the pattern, so you would need to do this in two steps or keep the trailing comma instead of converting to a leading comma.
Search string: (([^,]+, ){2}
Replacement string: $0\r
The {2} searches for exactly two consecutive matches of the specified pattern. If you use $1 instead of $0 in the replacement string, it will use only the second match for the replacement.
Drew

thanks !! this works fine for me. The trailing comma isn't a big issue

This reply was modified 2 years, 2 months ago by masterelaichi.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

drew.allen wrote:

I believe that this does close to what you are looking for. It can probably be massaged to do exactly what you want. The big problem is that the trailing , is included in the pattern, and there isn't an easy way to insert a \r within the pattern, so you would need to do this in two steps or keep the trailing comma instead of converting to a leading comma.
Search string: (([^,]+, ){2}
Replacement string: $0\r
The {2} searches for exactly two consecutive matches of the specified pattern. If you use $1 instead of $0 in the replacement string, it will use only the second match for the replacement.
Drew

Awesome! Thanks, Drew.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

dholajani wrote:

Jonathan AC Roberts wrote:
I would use an SSMS add-in like ApexSQL Refactor.
There is a free version of it here https://en.free downloadmanager.org/Windows-PC/ApexSQL-Refactor-FREE.html
I think you only get a trial period with the current version from the Apex site so better to use the download above.
Or if you have a budget and don't mind paying for something I would get Redgate SQL Prompt.
Thanks Man.

Considering what even some "paid for" software does (like hiding malware they didn't know about), I'm mighty leery about downloading and installing just about anything anymore. It's like playing volleyball in a mine field.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joshwelch Newbie Points: 1 More actions · Answer 9

Jonathan AC Roberts wrote:

I would use an SSMS add-in like ApexSQL Refactor.
There is a free version of it here https://en.freedownloadmanager.org/Windows-PC/ApexSQL-Refactor-FREE.html
I think you only get a trial period with the current version from the Apex site so better to use the download above.
Or if you have a budget and don't mind paying for something I would get Redgate SQL Prompt.

Is it work now days