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