FORMAT LONG SET OF VALUES QUICKLY

Many times it happens that we get a long list of tabular data in our requirement document and we format it manually line by line to comply with our code. Let’s find a way that will convert this line by manual work into Find and Replace kind of work.

Let’s say we have to use following list of data into T-Sql IN(…,…,…) clause. So every line should be formatted like ‘…’ with a comma (“,”) at the end of each line except the last line.

Gilbert Guy

Brown Kevin

Tamburello Roberto

Walters Rob

Walters Rob

Bradley David

Bradley David

Dobney JoLynn

Ellerbrock Ruth

To format such kind of values, we will take help of Find and Replace window efficiently.  Let’s see how.

First of all, I will copy these tabular data on sql query window and open the Find and Replace window

Take a sharp look on Find Options at Find and Replace window. There are 5 different options available. I will check the “Use:” option with “Regular expressions” option. Now click on expression builder button and select “^ Beginning of line” option and give “’” under Replace with: textbox.

Now click on Replace All button. Now the list of values will look like below.

'Gilbert Guy

'Brown Kevin

'Tamburello Roberto

'Walters Rob

'Walters Rob

'Bradley David

'Bradley David

'Dobney JoLynn

'Ellerbrock Ruth

Now again click on expression builder button and select “$ End of line” option and give “’,” under Replace with: textbox.

Now again click on Replace All button. Now the lists of values are formatted as per our need. We only need to remove last comma (,).

'Gilbert Guy',

'Brown Kevin',

'Tamburello Roberto',

'Walters Rob',

'Walters Rob',

'Bradley David',

'Bradley David',

'Dobney JoLynn',

'Ellerbrock Ruth'

I hope, today you have discovered a new power of Find  and Replace window  with me that will make your day to day life easier to format any line of data just by couple of clicks and save more time.

Let’s do one more click on Replace All button after putting expression as it's appear in the image.

See the output and let me know how much it is useful for your code?