How to sort alphanumeric string in SQL ?

Suppose we have a column with data in sql table below:

CT6
CT4
CT3
CT2
CT23
CT50
CT52
CT15
CT14
CT15
CT16


SELECT * FROM dbo.tbl_Data WHERE Status=1
ORDER BY 
LEFT(AlphaNumComun, PATINDEX('%[0-9]%', AlphaNumComun)-1) ASC,
CONVERT(INT, SUBSTRING(AlphaNumComun, PATINDEX('%[0-9]%', AlphaNumComun), LEN(AlphaNumComun))) ASC

Select next and previous id of record in sql with where condition

If you have a table named with Student and having records, and you want all records with next and previous PKId or id with each records and you can use lag and lead functions.

SELECT 
Student.PKId
Student.Name,
Student.Address,
NextPrev.PreviousId,  
NextPrev.NextId  
FROM Student
                   OUTER APPLY  
                   (select PreviousId,NextId  
                    from (  
                        select   
                               lag(PKId) OVER (ORDER BY Student.PKId DESC) as PreviousId,  
                               PKId,  
                               lead(PKId) OVER (ORDER BY Student.PKId DESC) as NextId  
                        from Student WHERE Status=1 --Here Condition should be matched with main condition
                    ) as t  
                    WHERE PKId=@PKId) AS NextPrev  
                                           WHERE Student.Status=1 AND Student.Name='Veer' --you can remove this condition if you want all records


--or simply with all records 

SELECT 
Student.PKId
Student.Name,
Student.Address,
NextPrev.PreviousId,  
NextPrev.NextId  
FROM Student
                   OUTER APPLY  
                   (select PreviousId,NextId  
                    from (  
                        select   
                               lag(PKId) OVER (ORDER BY Student.PKId DESC) as PreviousId,  
                               PKId,  
                               lead(PKId) OVER (ORDER BY Student.PKId DESC) as NextId  
                        from Student
                    ) as t  
                    WHERE PKId=@PKId) AS NextPrev  

Function For Comma separate string to Column in SQL Server

Here is a user defined function that we have created to convert comma separate string values to Table Column,
Mostly we use for IN statement in Select QUERY like

select * from Emp WHERE PKId IN(fn_SplitParameters('1,2,3',','))

CREATE FUNCTION [dbo].[fn_SplitParameters](@String varchar(max), @Delimiter char(1))           
returns @temptable TABLE (ID int,items varchar(max))           
as           
begin           
declare @idx int           
declare @slice varchar(max)           
 declare @ID int           
  set  @ID=0       
select @idx = 1           
if len(@String)<1 or @String is null  return           
           
while @idx!= 0           
begin           
set @idx = charindex(@Delimiter,@String)           
set @ID=@ID+1  
if @idx!=0           
set @slice = left(@String,@idx - 1)           
else           
set @slice = @String     
if(len(@slice)>0)      
insert into @temptable(ID,Items) values(@ID,@slice)           
set @String = right(@String,len(@String) - @idx)           
 if len(@String) = 0 break           
end       
return           
end

XML Data Insert,Update with MERGE in sql server

First of all you have to select XML data into a temptable, that will use as SOURCE table in merger statement.

SELECT
		ISNULL(x.v.value('Id[1]', 'int'), '') AS Id,
		ISNULL(x.v.value('Name[1]', 'bit'), '') AS Name,
		ISNULL(x.v.value('Address[1]', 'bit'), '') AS Address,
		ISNULL(x.v.value('Status[1]', 'nvarchar(max)'), '') AS Status,
		ISNULL(x.v.value('MoNumber[1]', 'nvarchar(max)'), '') AS MoNumber,
		ISNULL(x.v.value('Location[1]', 'nvarchar(max)'), '') AS Location,
		ISNULL(x.v.value('Email[1]', 'nvarchar(max)'), '') AS Email
		INTO #TempData
	FROM @XMLData.nodes('/ArrayOfEmployeeAccessModel/EmployeeModel') x (v);

And then you can user MERGER Statement to insert or update according to condition.

MERGE tbl_UpdatedEmployee AS TARGET
		USING #TempData AS SOURCE 
		ON (
		TARGET.PKId = SOURCE.Id 
		) 
		WHEN MATCHED  THEN 
		UPDATE 
		SET TARGET.Name = SOURCE.Name,
                    TARGET.Address= SOURCE.Address,
			
		WHEN NOT MATCHED BY TARGET THEN 
		INSERT (Name,Address,Status,MoNumber,Location,Email) 
		VALUES (SOURCE.Name,SOURCE.Address,SOURCE.Status,SOURCE.MoNumber,SOURCE.Location,SOURCE.Email)
		
Also if you want to get INSERTED or UPDATED rows or data than you can get as resultset, but for this first of all you have to declare a table variable on top then you can get output as table and return as resultset.
DECLARE @output TABLE (Id int,Email VARCHAR(50),Password NVARCHAR(10))
 In Above statement you can declare the columns name which you want to take as output columns, above defined a table variable.
 And now you have to add a statement at the last of merge statement, and you have to put ; (semicolon) at last of OUTPUT data statement.

MERGE tbl_UpdatedEmployee AS TARGET
		USING #TempData AS SOURCE 
		ON (
		TARGET.PKId = SOURCE.Id 
		) 
		WHEN MATCHED  THEN 
		UPDATE 
		SET TARGET.Name = SOURCE.Name,
                    TARGET.Address= SOURCE.Address,
			
		WHEN NOT MATCHED BY TARGET THEN 
		INSERT (Name,Address,Status,MoNumber,Location,Email) 
		VALUES (SOURCE.Name,SOURCE.Address,SOURCE.Status,SOURCE.MoNumber,SOURCE.Location,SOURCE.Email)

               OUTPUT INSERTED.Id,INSERTED.Address,INSERTED.Status INTO @output;
And now you can select your table variable as resultset.
SELECT * FROM @output

For More Detail about MERGE Visit Microsoft MSDN Weblink
Link

Insert Multiple ROWS in One SQL Query.


INSERT INTO [dbo].[tbl_Page]
           ([PageTitle]
           ,[Link]
           ,[Status]
           ,[CreatedDate]
           ,[Description])
     VALUES
           ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo');

Here remamber to put comma at end of value secton expect of last section of value, at the end of last section of value, you have to put semicollon to end the insert statement.

See the image for tested result.

Get data of current month only in sql

This is very Simple, Just get month of both of dates which dates you want to compare but both dates should be in date format or you have to cast in date format

get month of any date MONTH() method

MONTH('2019/02/22')

it will return only month of this date, just pass date in month method.

if your date in varchar or string format than you should cast in date format

CAST('2019/02/22'AS DATE)

Complete Example Is

MONTH(CAST(YourDate AS DATE)) = MONTH(GETDATE())