This type of problem is tricky to solve with simple replace functionality but becomes very easy with a regex function.
Sadly Microsoft haven't included this as a built in function for SQL Server but with some SQLCLR work it can be available.
SQL Server Regular expressions in T-SQL has an example of a SQLCLR function to search strings but here you would need a regex_replace function
using System.Data.SqlTypes;
namespace Public.SQLServer.SQLCLR
{
public class Regex
{
#region Regex_IsMatch Function
/// <summary>
/// Searches an expression for another regular expression and returns a boolean value of true if found.
/// </summary>
/// <param name="expressionToFind">Is a character expression that contains the sequence to be found. This expression leverages regular expression pattern matching syntax. This expression may also be simple expression.</param>
/// <param name="expressionToSearch">Is a character expression to be searched.</param>
/// <param name="start_location">Is an integer expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.</param>
/// <returns>Bit.</returns>
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlBoolean Regex_IsMatch(SqlString expressionToFind, SqlString expressionToSearch, SqlInt32 start_location)
{
// Process expressionToFind parameter
string etf;
if (expressionToFind.IsNull)
{
return SqlBoolean.Null;
}
else if (expressionToFind.Value == string.Empty)
{
return new SqlBoolean(0);
}
else
{
etf = expressionToFind.Value;
}
// Process expressionToSearch parameter
string ets;
if (expressionToSearch.IsNull)
{
return SqlBoolean.Null;
}
else if (expressionToSearch.Value == string.Empty)
{
return new SqlBoolean(0);
}
else
{
ets = expressionToSearch.Value;
}
// Process start_location parameter
int sl;
if (start_location.IsNull)
{
sl = 0;
}
else if (start_location.Value < 1)
{
sl = 0;
}
else
{
sl = (int)start_location.Value -1;
if (sl > expressionToSearch.Value.Length + 1)
{
sl = expressionToSearch.Value.Length;
}
}
// execute the regex search
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(etf);
return regex.IsMatch(ets, sl);
}
#endregion
#region Regex_Replace Function
/// <summary>
/// Replaces all occurrences of a specified regular expression pattern with another regular expression substitution.
/// </summary>
/// <param name="expression">Is the string expression to be searched.</param>
/// <param name="pattern">Is a character expression that contains the sequence to be replaced. This expression leverages regular expression pattern matching syntax. This expression may also be simple expression.</param>
/// <param name="replacement">Is a character expression that contains the sequence to be inserted. This expression leverages regular expression substitution syntax. This expression may also be simple expression.</param>
/// <returns>String of nvarchar(max), the length of which depends on the input.</returns>
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString Regex_Replace(SqlString expression, SqlString pattern, SqlString replacement)
{
// Process null inputs
if (expression.IsNull)
{
return SqlString.Null;
}
else if (pattern.IsNull)
{
return SqlString.Null;
}
else if (replacement.IsNull)
{
return SqlString.Null;
}
// Process blank inputs
else if (expression.Value == string.Empty)
{
return expression;
}
else if (pattern.Value == string.Empty)
{
return expression;
}
// Process replacement parameter
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(pattern.Value);
return regex.Replace(expression.Value, replacement.Value);
}
#endregion
}
}
Once available you can achieve your result with a query like the following;
select [library].[Regex_Replace]('String with many odd spacing
issues.
!','\s{1,}',' ')
which returns
String with many odd spacing issues. !
the expression \s{1,} means match any whitespace \s in a sequence of one or more {1,} and matches are replaced with your single space character.
There is more to using SQLCLR than the code included here and further research into creating the assemblies and SQLCLR functions is required.
