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

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 #1 – Find duplicate records

Sometimes we need to detect duplicate rows in a SQL server table. Today I am going to show you different ways to achieve this.

Our sample table has 100001 records, and we will find duplicate records based on the Product name.

 

SQL 1:


SELECT Main.*
FROM Product Main
CROSS APPLY
(
Select TOP 1 ProductId,ProductName
FROM Product
WHERE ProductName = Main.ProductName
AND Main.ProductId > ProductId
ORDER BY ProductId ASC

) AS Sub

 

SQL 2:

SELECT Main.* FROM Product Main
CROSS APPLY
(
Select Min(ProductId) AS ProductId
FROM Product WHERE ProductName = Main.ProductName
) AS Sub
WHERE Main.ProductId > Sub.ProductId

 

SQL 3:


SELECT * FROM
(
Select RANK()
OVER(Partition BY ProductName
ORDER BY ProductId) as Rnk, *
FROM Product
) AS Main
WHERE Main.Rnk >= 2

 

SQL 4:


SELECT
ProductId,ProductName,Qty,CategoryId
FROM (
SELECT
ROW_NUMBER()
OVER (PARTITION BY ProductName
ORDER BY ProductId) AS RowId,*
FROM Product
) AS Wrap
WHERE RowId <> 1

 

SQL 5:


SELECT DISTINCT Main.*
FROM Product Main
CROSS APPLY
(
Select ProductId,ProductName
FROM Product
WHERE ProductName = Main.ProductName
AND Main.ProductId > ProductId

) AS Sub

 

SQL 6:


SELECT
Main.*
FROM (
SELECT ProductName,
MIN(ProductId) AS FirstProductId
FROM Product
GROUP BY ProductName
HAVING COUNT(1) > 1
) AS Sub
INNER JOIN Product Main ON Main.ProductName = Sub.ProductName
WHERE Sub.FirstProductId < Main.ProductId

 

So far I have found that SQL 6 is performing well. However the results could vary in other environments based on the server resources and table indexing.

Please let me know if you have other ideas to achieve this.