Following SQL can be used in order to format a number with leading zeros.
The trick is to use STR function with REPLACE.
Character representation of a number can be achieved using STR function. If the result is less than specified length, then leading spaces are added. So next trick is to use REPLACE function to replace spaces with zeros.
DECLARE @number INT
DECLARE @leadingZero INT
SET @number = 123
SET @leadingZero = 10
SELECT REPLACE (STR( @number, @leadingZero ), ' ' , '0' )
This is not a common database scenario where database developer will have to parse a file path and extract file name and it’s extension. Most of the time it will be done in the application level.
You can consider the following if you ever need this:
DECLARE @filePath varchar (1000)
DECLARE @ext varchar(5)
DECLARE @fileName varchar(255)
DECLARE @folderName varchar(255)
--full file path
SET @filePath = 'C:\Users\sbarua\Documents\eBook\shamika.bmp'
SET @fileName = Right(@filePath,CHARINDEX('\',REVERSE(@filePath))-1)
SET @ext = Right(@filePath,CHARINDEX('.',REVERSE(@filePath))-1)
SET @folderName = REPLACE(@filePath,@fileName,'')
In order to iterate through the all the records in a table without cursor following approach could be a help. Please replace SampleTable and SampleTableID with your preferred Table info.
IF OBJECT_ID('tempdb..#memTable') IS NOT NULL
DROP TABLE #memTable
SELECT ROW_NUMBER() OVER (ORDER BY [SampleTableID] ASC)
DECLARE @ctr INT = 1
DECLARE @recordCount INT = (SELECT COUNT(1) FROM #memTable)
WHILE (@ctr <=@recordCount)
FROM #memTable WHERE RowNo = @ctr
SET @ctr = @ctr + 1
Finding random records from a table might not be the important one. However, if you ever find yourself in a position to find random records from a table, then following could be a help:
Below sql queries will return random three(3) records from a table
Method 1: Using NEWID()
NEWID() will help us to generate random result set in a simple way. It creates a unique value of type uniqueidentifier. It will unique for each call. When it is a part of ORDER BY clause then it generates a unique value in memory for each row and then sorted in ascending order.
SELECT TOP 3 * FROM Customer ORDER BY NEWID()
Method 2: Using Tablesample()
TABLESAMPLE can quickly return a sample from a large table. TABLESAMPLE clause can be added to any table in the FROM clause. Percentage of rows or number of rows need to be specified as a part of this clause. However, this sampling method will return an approximate number of rows back. If you specify a small number, you might not be able to see any results. Rows returned by this clause are not in order and most likely to be random.
SELECT TOP 3 * FROM Customer Tablesample(1 percent)
If you want to get trully random records then user NEWID().