Adding leading zeros to int column

Hi there,

some of my imports needs by design for the importcounter not integers like 1, 2, 3, 4, 5,...
they expect varchar with leading zeros. Today I will show you how to add leading zeros
to your integers by a simple combination of cast() and right().

Let me explain the function:
RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)), (select cast(LEN(max(itemno)) as int) from [article])) as ImportID

First of all
'0000000000000000000000'+cast([Itemno] as varchar (255)
this adds leading zeros before the int field which we cast to varchar(255)
after that we use the right function to take the first X chars from the right side of string.
RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)), X)
but my imports need as input the a variable lenght addicted by the lenght of maximum int value,
so I replaced the X with select cast(LEN(max(itemno)) as int) from [article] this query returns for the demodata 5 because the max number used is 50000 and len of 50000 = 5.
This way i got my importers working.

Copyable Version:
-- Table with int values
SELECT    [Itemno] ImportID,
        [Itemname],
        [Price]
  FROM [article]

-- Table with leading zeros as varchar
SELECT    RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)),
        (select cast(LEN(max(itemno)) as int) from [article])) as ImportID,
        [Itemname],
        [Price]
  FROM [article]

Temporary Tables vs. Common Table Expression (CTE)

Today I want to show you how to use CTE and explain the difference
between CTE and a temporary tables.

Temporary Table
local temporary table
Local temporary tables (starting with a single #) are created in the tempdb and are only available in the current session. After closing the session the are dropped automatically.
global temporary table
Global temporary table (staring with two #) are created in the tempdb and are available in all sessions. They are automatically dropped after all user connections are closed.

CTE
CTE is not a table it is a temporary result set with scope on the current query, so it is not limited by the session it is limited by the current select.

Difference to a temporary table
CTE is a temporary result created in memory not in tempdb.
CTE cannot have an index.
CTE is only for the current statement.
CTE is faster (no table Creation)



Lets have a look at the execution plans
CTE (Subtreecost 0,313466)

local temp (Subtreecost 11,7748 for creating temptable + 0,313466 for select from temptable = 12,088266)



global temp (Subtreecost 11,7748 for creating temptable + 0,313466 for select from temptable = 12,088266)



As you can see CTE is not as expensive as temp tables but it can't be indexed and is pointed to current statement.
You should use CTE for inline subquerys or aggregating. For complex procedures or scripts you have to use temptables.

Copyable Version:
-- CTE
;With CTEResult(Itemno, itemname, Price)
AS
(
SELECT Itemno, itemname, Price from article a
)
SELECT * FROM CTEResult cr
WHERE cr.Itemno in (1,2,50)
ORDER BY cr.itemname
-- localtemptable
SELECT Itemno, itemname, Price into #localtemptable from article a
select * from #localtemptable where itemno in (1,2,50) order by Itemname
-- globaltemptable
SELECT Itemno, itemname, Price into ##globaltemptable from article a
select * from ##globaltemptable where itemno in (1,2,50) order by Itemname


drop table #localtemptable
drop table ##globaltemptable

Cut off leading zeros on numbers in varchar field

Today we want to get rid of leading zeros in numbers on a varchar column.
To customize it you need to replace the ~ with a char that is not used in your column.
See my code below:



Copyable Version:
CREATE Table [dbo].[Leading_Zeros](
    [INT] [bigint] NULL,
    [Varchar] [varchar](250) NULL
    ) ON [PRIMARY]
   
GO

INSERT INTO [Leading_Zeros]
Values    (1, '001'),
        (2, 'Zero0000Zero'),
        (3, '156000748465'),
        (4, '000555000555000'),
        (5, 'TestString'),
        (6, 'Test00000String')
       
select    *,
        REPLACE(REPLACE(LTRIM(REPLACE(REPLACE([Varchar], ' ', '~'), '0', ' ')), ' ', '0'), '~', ' ') Leading_Zeros
from [Leading_Zeros]

The Hashbyte Function

Today I got a little problem with a developer that wants to use the Hashbyte function. After a little discussion and reading we saw that the output of the function ist varbinary so there is no way to put it into a varchar field without different results. See the Example:


So if you want to use the Hashbyte function you have to use the right data type.

Copyable Version:
DECLARE @HashThis nvarchar(40)
DECLARE @HashValue nvarchar(42)
DECLARE @HashValue2 varbinary(42)

set @HashThis = 'myteststring'
SELECT HASHBYTES('SHA1', @HashThis) as 'Auto data type'

set @HashValue=HASHBYTES('SHA1', @HashThis)
SELECT @HashValue as 'Varchar data type'

set @HashValue2=HASHBYTES('SHA1', @HashThis)
SELECT @HashValue2 as 'Varchar data type'