RegEx to remove whitespace from qualified column names in SQL Server

Question

I built a regex to remove whitespace from qualified column names in SQL server. There could be a bunch of these in a single input string For example,

SELECT * 
FROM dbo.[bad column name]
UNION
SELECT * 
FROM dbo.[bad column name2]

should be

SELECT * 
FROM dbo.[badcolumnname]
UNION
SELECT * 
FROM dbo.[badcolumnname2]

Here is the regex search string: (\[\w*)( )([^\[\]]*?\])

Regex Replacement: $1$3

But the problem is that it needs to be called many times to replace all the whitespace in a column name.

I.e.

[bad column name]

Becomes

[badcolumn name]

Then finally the desired output

[badcolumnname]

After two successive replacement operations

What is a good way to make this regex so that it does not have to be iterative?

is there a reason why you have to use regex? if you just want to remove white spaces, can you just do a replace(), which has options to replace occurrences. — chungtinhlakho, Jun 07 '16 at 21:55
I am trying to remove all whitespace between '[' and ']' brackets — nh43de, Jun 07 '16 at 21:55
To give some motivation, the purpose of this is to remove whitespace from bracketed column names in a bunch of sql code — nh43de, Jun 07 '16 at 21:56
can you show me the sql statement that you use this (\[\w*)( )([^\[\]]*?\]) with? thanks — chungtinhlakho, Jun 08 '16 at 00:14

score 1 · Answer 1 · edited May 23 '17 at 12:33

If you at least knew the maximum number of words in a label you could use replacement such as this

\[((\w+)\s?)?((\w+)\s?)?((\w+)\s?)?((\w+)\s?)?((\w+)\s?)?\]

[$2$4$6$8$10]

https://regex101.com/r/uB5fQ4/2

If you don't care about the regex being a tiny bit longer, you can use non-capturing groups to avoid capturing the groups you dont need so you dont have to skip them.

\[(?:(\w+)\s?)?(?:(\w+)\s?)?(?:(\w+)\s?)?(?:(\w+)\s?)?(?:(\w+)\s?)?\]

[$1$2$3$4$5]

https://regex101.com/r/wC1iX2/1

Otherwise you should probably use a parser instead of a regex, since even if you use the global modifier /g each time a match is found the capture group is overwritten.

You can see how that happens here: https://regex101.com/r/iN2rD4/2

Here's one way to achieve the results you want with javascript:

https://jsfiddle.net/yosefh/0ohrno4L/1/

<body>
  <div id="result" ></div>
  <script>
  var str = '[bad column name]';
  var result = str.replace(/\s+/g,'');

  document.getElementById("result").innerHTML = result;
  </script>
</body>

and in PHP

$str = '[bad column name]';
$result = preg_replace('/\s+/', '', $str);

You can see options for parsing here

How do I regex match with grouping with unknown number of groups

and here:

How to capture an arbitrary number of groups in JavaScript Regexp?

Experiment with regex here: https://regex101.com/

RegEx to remove whitespace from qualified column names in SQL Server

1 Answers1