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:

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