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.
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