spaces

How to use the Space() function in MSSQL

 

To learn how to use the Space() function in MSSQL you can take a look at the following snippet.

 

Let’s say you have a table (tblTest) like this:

ID Testcolumn
13 X000013
14 X000014
15 X000015

 

You want to select the next “X” number for Testcolumn. The problem is that when adding something to 000015 you will loose the 0 characters. So after getting the information that 16 is the next highest number you need to fill the gap between X and 16 with 0. And if the length of the X number is not fixed, you need to do that generic. The Space() function can help you alot. What the space function does is, it just creates as much spaces as you need. We will use this to replace the Spaces then with 0.

Sample MSSQL

SELECT TOP 1 Testcolumn AS [Currently Highest Field Value],

CONCAT(
	LEFT(Testcolumn,1), -- select X
	REPLACE( --Replace *
	SPACE(LEN(Testcolumn) - 1 - LEN(CONVERT(BIGINT,SUBSTRING(Testcolumn,2,LEN(Testcolumn))))) -- adds the missing characters as Space
	,' ','0') -- * each created SPACE() with a 0
	, 
	CONVERT(BIGINT,SUBSTRING(Testcolumn,2,LEN(Testcolumn))) --select 15
	+1 -- adds 1
	) AS  VALUE
    
FROM tblTest 
ORDER BY SUBSTRING(testcolumn,2,LEN(testcolumn)) DESC

The result will be

X000016

How to replace multiple spaces with a single space in c# or vb.net

 

To replace multiple spaces with a single space in c# or vb.net you can use a Regular Expression

Sample c#

var inputString = "This  is    a Text with    too much     spaces   in   it!!!";
inputString = Regex.Replace(inputString, @"[ ]{2,}", @" ", RegexOptions.None);

Sample VB.NET

Dim inputString = "This  is    a Text with    too much     spaces   in   it!!!"
inputString = Regex.Replace(inputString, "[ ]{2,}", " ", RegexOptions.None)

there are more ways to do that, but i personally prefer the regex way.
A other way to archieve that would be to use a loop. The snippet below is replacing double spaces until there are no double spaces left.

 

Sample c#

var inputString = "This  is    a Text with    too much     spaces   in   it!!!";
while (inputString.Contains(@"  "))
{
    inputString = inputString.Replace(@"  ", @" ");
}

Sample VB.NET

Dim inputString = "This  is    a Text with    too much     spaces   in   it!!!"
While inputString.Contains("  ")
	inputString = inputString.Replace("  ", " ")
End While

playing with the RegexOptions can speed up or slow down the regex snippet a bit, see RegexOptions Enumeration in the MSDN