SQL Tip #13 – Padding a number with leading Zeros

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' )

SQL Tip #12 – Get File Extension, File Name and Folder path

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 variable
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,'')

--display
SELECT @fileName
SELECT @ext
SELECT @folderName

SQL Tip #11 – Loop through all records in a Table using WHILE

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
BEGIN
	DROP TABLE #memTable
END


SELECT ROW_NUMBER() OVER (ORDER BY [SampleTableID] ASC) 
       AS RowNo
       ,*
INTO #memTable
FROM [dbo].SampleTable 



DECLARE @ctr INT = 1
DECLARE @recordCount INT = (SELECT COUNT(1) FROM #memTable)
             

WHILE (@ctr <=@recordCount)
BEGIN

       SELECT
          *
       FROM #memTable WHERE RowNo = @ctr

        SET @ctr = @ctr + 1
END

SQL Tip #8 – Only Unique Records

Once I had the requirements to pick a customer per city. In order to solve this problem I came up with the following solutions.

Sample table 1:

Output 1:

 

Solution 1:

SELECT Main.* FROM Customer Main
INNER JOIN
	(
		SELECT MIN(CustomerID) AS CustomerId
		FROM Customer GROUP BY City
	) Sub
ON Main.CustomerId = Sub.CustomerId
ORDER BY Main.CustomerId

 

Solution 2:

SELECT Main.* FROM Customer Main
CROSS APPLY
	(
		SELECT MIN(CustomerID) AS CustomerId
		FROM Customer
		WHERE City = Main.City
		GROUP BY City
	) Sub
WHERE Main.CustomerId = Sub.CustomerId
ORDER BY Main.CustomerId

 

Solution 3:

SELECT Main.* FROM Customer Main
CROSS APPLY
	(
		SELECT ROW_NUMBER()
		OVER(PARTITION BY City
		     ORDER BY CustomerId ASC) AS Rnk,
		CustomerId
		FROM Customer
	) Sub
WHERE Main.CustomerId = Sub.CustomerId
      AND Sub.Rnk = 1
ORDER BY Main.CustomerId

 

Solution 4:

SELECT CustomerId,CustomerName,City FROM
(
		SELECT ROW_NUMBER()
		OVER(PARTITION BY City
		     ORDER BY CustomerId ASC) AS Rnk,
		*
		FROM Customer
) Main
WHERE Main.Rnk = 1
ORDER BY Main.CustomerId

 

Performance of these query will vary based on the database size, indexing etc. You can pick the query that suits you most.