Generate AlphaNumeric Identity Code

    In all sorts of companies, employee ID or the ID's of clients from an Online Trading firm, its very common to have an Alphanumeric ID.But from database perspective, it is better to keep a numeric ID. Any way if you like to generate an Alphanumeric Key, then you have to apply your logics because there isn't any built function for this pupose in MS SQL SERVER. Below two methods could do the job:

--------------------------1st Method-----------------------------

CREATE TABLE MyTestTable

(

 ID         INT IDENTITY (1,1)

,UserID     AS 'E'+RIGHT('000000' + CONVERT(VARCHAR,ID),6)

,UserName   VARCHAR(100)

,City       VARCHAR(100)

,ContactNo  VARCHAR(15)

)




--- Insert the
rows into the table




INSERT INTO MyTestTable VALUES
('Amit','Mumbai','88888888')

INSERT INTO MyTestTable VALUES
('Muthu','Chennai','77777777')




---- Output




SELECT * FROM MyTestTable




ID    UserID      UserName City      ContactNo

1     E000001     Amit   Mumbai     
88888888

2     E000002     Muthu  Chennai 77777777

 --------------------------2nd Method-----------------------------

Now if we don't want to keep the Identity Column and only want to keep the Generated Column, then we can get a new ID with the help of below stored Procedure:

CREATE PROC [dbo].[usp_NewCode]

      @InPrefix         VARCHAR(5),

      @InTableName      VARCHAR(100),

      @InColumnName     VARCHAR(100),

      @InIDLength       INT,  

      @OutCode          VARCHAR(20) OUTPUT

AS

BEGIN

   DECLARE @STR
VARCHAR(100)

   ,@INTPART INT, @SQL NVARCHAR(MAX)

   ,@Parameter_Definition NVARCHAR(MAX)

   ,@Zeros VARCHAR(12)

                              

   SET @Zeros = '0000000000000000000000'

                              

   SET @SQL = N'SELECT @MYSTR =
ISNULL(MAX('

   SET @SQL = @SQL + @InColumnName

   SET @SQL = @SQL + '),''00000'') FROM '

   SET @SQL = @SQL + @InTableName

   SET @SQL = @SQL + ''             




                              

  SET @Parameter_Definition
= N'@MYSTR
NVARCHAR(100) OUTPUT'

  EXECUTE SP_EXECUTESQL @SQL,
@Parameter_Definition, @MYSTR = @STR OUTPUT




  SELECT @STR = SUBSTRING(@STR,1+LEN(@InPrefix),LEN(@STR)-LEN(@InPrefix))

  SELECT @INTPART = CONVERT(INTEGER,@STR)

  SELECT @INTPART = @INTPART+1

  SELECT @OutCode = CONVERT(VARCHAR,@INTPART)

  SELECT @OutCode = (@InPrefix +SUBSTRING(@Zeros,1,@InIDLength-LEN(@OutCode)-LEN(@InPrefix))+
@OutCode)




END
 

 This SP can be used for any number of tables within a database, also the length of the ID is variable. Suppose we are creating a new table MyTestTable where we are giving an UserID column which should be of 8 character length and prefixed with 'EMP' :

CREATE TABLE MyTestTable

(

 UserID     VARCHAR(10)

,UserName   VARCHAR(100)

,City       VARCHAR(100)

,ContactNo  VARCHAR(15)

)




--- Insert the
first row




BEGIN TRY

BEGIN TRAN




  DECLARE @ID VARCHAR(20)

  EXEC [USP_NewCode] 'EMP','MyTestTable','UserID',8, @ID OUTPUT




  INSERT INTO
MyTestTable VALUES (@ID,'Amit','Mumbai','88888888')




  COMMIT TRAN

END TRY     

BEGIN CATCH

  ROLLBACK TRAN

END CATCH




--- Insert the
second row




BEGIN TRY

BEGIN TRAN




  DECLARE @ID VARCHAR(20)

  EXEC [USP_NewCode] 'EMP','MyTestTable','UserID',8, @ID OUTPUT




  INSERT INTO
MyTestTable VALUES (@ID,'Muthu','Chaennai','77777777')




  COMMIT TRAN

END TRY     

BEGIN CATCH

  ROLLBACK TRAN

END CATCH




------- Output




SELECT * FROM MyTestTable




UserID      UserName City     ContactNo

EMP00001    Amit      Mumbai   
88888888

EMP00002    Muthu     Chennai  77777777

Reference: Govind Badkur(http://govindbadkur.blogspot.com)

FIRST AND LAST Day of Month , Year

In SQL, very often we need to find First Day or Last Day of Year, Quarter, Month or Week, when we develop in SQL Server. To make it simple we will get it from below :

 

----Today
SELECT GETDATE()

----Yesterday
SELECT DATEADD(d,-1,GETDATE())

----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)

----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)

----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))

OR

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,-1)

----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

-----Monthly Week No.

SELECT DATEPART(WW,GETDATE())-DATEPART(WW,DATEADD(DD,1-DAY(GETDATE()),GETDATE())) + 1

----First Day Of Current Quarter
SELECT DATEADD(QQ, DATEDIFF(QQ,0, GETDATE()), 0)

---Last Day Of Current Quarter

SELECT DATEADD(MS,-3,DATEADD(QQ, DATEDIFF(QQ,0, GETDATE())+1, 0))

----First Day Of Prior Quarter
SELECT DATEADD(QQ, DATEDIFF(QQ,0, GETDATE())-1, 0)

----Last Day Of Prior Quarter
SELECT DATEADD(MS,-3,DATEADD(QQ, DATEDIFF(QQ,0, GETDATE()), 0))

---TO GET THE MONTH NUMBER FROM A MONTHNAME:

SELECT DATEPART(mm,CAST('JUL'+ ' 1900' AS DATETIME))

---Convert an YEAR into a DATETIME:

SELECT CAST('2015' AS DateTime)

---Convert a Month Number into Month Name :

SELECT DATENAME(MM, STR(@MONTH) + '/1/1900')

And so on ......

 Reference: Govind Badkur(http://govindbadkur.blogspot.com)

Get Last Part of a String in SQL

   Sometime we need to get the last part of a string which may contain a char delimiter as a separator or a substring as a separator. If the separator is a char delimiter, then the task goes simple, we just need to Reverse the string and the get the substring from begining to the to the first occurance of the delimiter and then again Reverse the Outputted substring.

    Let's say, we have a string "India,Britain,Australia,Canada,Italy", and want to get "Italy" as the last substring, then write the query as below :

DECLARE @String VARCHAR(MAX)

SET @String = 'India,Britain,Australia,Canada,Italy'

SET @String = REVERSE(@String)




SELECT REVERSE(SUBSTRING(@String,1,CHARINDEX(',',@String,1)-1))


 ----- Output :
 Italy

     If the string has delimiter char at the end of the string as well, that means our string is @String = "India,Britain,Australia,Canada,Italy," then increament the position by 1, as below:

DECLARE @String VARCHAR(MAX)

SET @String = 'India,Britain,Australia,Canada,Italy,'

SET @String = REVERSE(@String)







SELECT REVERSE(SUBSTRING(@String,2,CHARINDEX(',',@String,2)-2))


 ----- Output :

 Italy

   Now, if the separator is a pattern or substring, then make use of PATINDEX as below :

DECLARE @STRING VARCHAR(MAX)

SET @STRING = 'NOT FOUND ONMay  8 2012 12:07PM<br/>NOT FOUND
ONMay  8 2012
12:09PM<br/>May  8 2012  1:08PM<br/>May  8 2012 
1:17PM<br/>May  8 2012  1:25PM<br/>Cash Receipt Found In IMS
Done On : May  8 2012  2:01PM'

   

SELECT REVERSE(SUBSTRING(REVERSE(@STRING),1,PATINDEX('%>/rb<%',SUBSTRING(REVERSE(@STRING),1,LEN(@STRING)-1))-1))


----- Output :
Cash Receipt Found In IMS Done On : May  8 2012  2:01PM

     Again, if the string has separtor substring at the end as well, then increament the position by length of the separtor subsstring, see below:

DECLARE @STRING VARCHAR(MAX)

SET @STRING = 'NOT FOUND ONMay  8 2012 12:07PM<br/>NOT FOUND ONMay  8 2012
12:09PM<br/>May  8 2012  1:08PM<br/>May  8 2012 
1:17PM<br/>May  8 2012  1:25PM<br/>Cash Receipt Found In IMS
Done On : May  8 2012  2:01PM<br/>'



SELECT REVERSE(SUBSTRING(REVERSE(@STRING),6,PATINDEX('%>/rb<%',SUBSTRING(REVERSE(@STRING),6,LEN(@STRING)-6))-1))


 ----- Output :
Cash Receipt Found In IMS Done On : May  8 2012  2:01PM

Reference: Govind Badkur(http://govindbadkur.blogspot.com)