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
NULL indicates missing/unknown/inapplicable/absent.
If the above statement makes you think that NULL is useless, then you are mistaken. This is one of the thing that no database developer can avoid it. So it very important to understand how NULL behaves differently in various situations.
Simply we can say, NULL is a placeholder for missing or unknown values at any given time.
At the time of the table creation if we know that values for certain columns won’t be known or will be missing during record insertion then we can make those columns to allow NULL. Later NULL could be replaced with the meaningful values.
Takeaway for NULL:
- It is very important not to get confused NULL as an space or empty string.
- NULL is not equal to NULL.
- Any operation involving NULL will return NULL
- Comparison operators (<, >, =, <>) cannot be used to find records with the NULL values. IS NULL or IS NOT NULL operators is the way to go.
- NULL will be ignored for most of the aggregate functions. However, COUNT(*) will include NULL values.
Following examples below will return NULL:
SELECT 'Sumon' + NULL
SELECT 0 + NULL
SELECT 10 * NULL