Cleaning up a SQL SP with Regex

Question

1) If I am running a find and replace in SQL 2005 - what would be the regular expression to find tab and space sequences ( or space and tab sequences ) and replace them with just tab?

2) If I have a line which begins with a space - is there a regular expression to convert that leading space to a tab?

3) What would be the regular expression to remove all of the spaces before a CR/LF in a SQL statement?

TIA for the help - I know this will be trivial to most of you, Doug

Answer 1

Question 1

UPDATE Customers SET Title = Replace(Title, '\t ', '\t') AND Title = Replace(Title, ' \t', '\t');

Taken From: http://www.sqlbook.com/SQL/SQL-Replace-Function-25.aspx

See if that solves your problem.. Ive never tried it though :(

You might have to perform 2 queries if the AND operator dont work :D

Answer 2

found something at CodeProject:

^[ \t]+|[ \t]+$

hope that helps!

Answer 3

If you're talking about the Quick Replace dialog in SQL Server Management Studio then this gets any combination of two or more spaces and/or tabs.

:b:b+

This finds all leading spaces/tabs (remove the + if you only want to match a single one)

^:b+

And this gets you all trailing spaces/tabs

:b+$

Stick \t in the Replace with: box to convert matches to a single tab.

Answer 4

Of course do not type in the ' in the examples below:

Spaces before a CRLF - in SQL management Studio

find what: ' \n' replace with: '\n' check use: regular expressions

--> How do you specify 1 or more spaces?

Spaces before a TAB

find what: ' \t' replace with: '\t' check use: regular expressions

--> How do you specify 1 or more spaces?

Spaces after a TAB

find what: '\t ' replace with: '\t' check use: regular expressions

--> How do you specify 1 or more spaces?

Empty Lines - TABs and CRLF

find what: '\t\n' replace with: '\n' check use: regular expressions

--> How do you specify 1 or more tabs?