Get Day,month,year from Date using datepart( ) Function in SQL Server

Use this function to get the Year of current Date:
datepart(yy,date)

Use this function to get the month of current Date:
datepart(mm,date)

Use this function to get the day of current Date:
datepart(dd,date)


Ex:

Select Datepart(dd,admissiondate) from Student;

This print the day of the date in the  'admissiondate' column

Create SQL Database Dynamically using C# code

Use below Code to Create SQL Database and create .mdf and .ldf file to your desired location :

Note: In the connection string , Initial Catalog should be 'master'

'myDB' will be by any name , which u want to like as database name.

SqlConnection con = new SqlConnection("Data Source=yourservername;Initial Catalog=master;Integrated Security=True");

SqlCommand com = new SqlCommand();

string db_str = "CREATE DATABASE myDB ON PRIMARY " +
       "(NAME = myDB _Data, " +
       "FILENAME = 'C:\\myDB.mdf', " +
       "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
       "LOG ON (NAME = myDB _Log, " +
       "FILENAME = 'C:\\myDB.ldf', " +
       "SIZE = 1MB, " +
       "MAXSIZE = 5MB, " +
       "FILEGROWTH = 10%)";

con.Open();com.Connection = con;

com.CommandText = db_str;

com.ExecuteNonQuery();

con.Close();

After compile this Code:

Database myDB Created to the SQL, and myDB.mdf and myDB.ldf created in the C Drive as the path given in the code.

Query to Create Dynamically com.Parameters.Add - SQL

Select +'com.Parameters.AddWithValue("@'+COLUMN_NAME+' ", dt_results.Rows[i]["'+COLUMN_NAME+'"]); ' from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTableName'


This Gives Output :

                    com.Parameters.AddWithValue("@ItemId", dt_results.Rows[i]["ItemId"]);
                    com.Parameters.AddWithValue("@GlobalId", dt_results.Rows[i]["GlobalId"]);
                    com.Parameters.AddWithValue("@ProductId", dt_results.Rows[i]["ProductId"]);
                    com.Parameters.AddWithValue("@CategoryId", dt_results.Rows[i]["CategoryId"]);
                    com.Parameters.AddWithValue("@CategoryName", dt_results.Rows[i]["CategoryName"]);
                    com.Parameters.AddWithValue("@Location", dt_results.Rows[i]["Location"]);
                    com.Parameters.AddWithValue("@PostalCode", dt_results.Rows[i]["PostalCode"]);
                    com.Parameters.AddWithValue("@Title", dt_results.Rows[i]["Title"]);
                    com.Parameters.AddWithValue("@Price", dt_results.Rows[i]["Price"]);
                    com.Parameters.AddWithValue("@SellingState", dt_results.Rows[i]["SellingState"]);
                    com.Parameters.AddWithValue("@TimeLeft", dt_results.Rows[i]["TimeLeft"]);
                    com.Parameters.AddWithValue("@ShippingType", dt_results.Rows[i]["ShippingType"]);
                    com.Parameters.AddWithValue("@Currency", dt_results.Rows[i]["Currency"]);
                    com.Parameters.AddWithValue("@ShipToLocations", dt_results.Rows[i]["ShipToLocations"]);
                    com.Parameters.AddWithValue("@HandlingTime", dt_results.Rows[i]["HandlingTime"]);

Query to Merge All Columns Separated with comma -SQL Server

This Query Gives all columns in a single column Separated with comma.

SELECT STUFF((SELECT COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' for xml path('')),1,0,'')

You can Replace the comma with any symbol by which u want to separate.


'TableName' must be replaced by your table name.

Query to Create Columns & Parameters values for Insert Command in Stored Procedure.-SQL

INSERT INTO TableName(name,address,........) VALUES ('@name','@address',...........)

In the Insert Command, we have to Write a large no. of Columns & parameter from their values comes.It takes a lot of time and energy..

Query to Create Columns and Parameters dynamically on 1 click...

Query to Create Columns,  like :   name,address,..........

SELECT STUFF((SELECT COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTableName' for xml path('')),1,0,'')

This Query gives all comma separated columns



Query To Create Parameters from values comes ,like @name,@address...

SELECT STUFF((SELECT '@'+COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTableName' for xml path('')),1,0,'')


This Query Gives all Comma Separated Parameters


NOTE :  Replace YourTableName with your table name

SQL Query to create Parameters for Stored Procedure

QUERY :

SELECT +'@'+COLUMN_NAME+' '+DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH as varchar)+')'+',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='yourTableName'


Table contains the columns must replace the 'YourTableName' in above query.

This Query Concatenates the Column Name , Datatype and size with the hard coded symbols like '@' ',' ('  and ')'

This Gives the Output in table form, You can copy amd paste it in stored procedure.


OUTPUT:

@ItemId nvarchar(100),
@GlobalId nvarchar(100),
@ProductId nvarchar(100),
@CategoryId nvarchar(100),
@CategoryName varchar(200),
@Location varchar(200),
@Title varchar(500),
@Price nvarchar(200),
@SellingState varchar(100),
@TimeLeft nvarchar(100),
@ShippingType varchar(200),
@Currency varchar(100),
@ShippingServiceCost nvarchar(200),
@ShipToLocations varchar(200),
@OneDayShippingAvail varchar(50),
@ReturnAccept varchar(50),
@ItemUrl nvarchar(200),
@ListingType varchar(200),
@BidCount varchar(200),
@BuyItNowAvail varchar(50),
@BuyItNowPrice nvarchar(200),
@BestOfferEnable varchar(50),
@GalleryUrl varchar(100),
@PaymentMode varchar(200),

SQL Query to Rename Column of a Table

EXECUTE QUERY TO RENAME THE COLUMN :

 sp_RENAME 'YourTableName.OldColumnName', 'NewColumnName' , 'COLUMN'


 FOR EX:

  sp_RENAME 'Student_details.Division', 'Grade' , 'COLUMN'


This Query, Rename the Column 'Division' to the 'Grade' in the 'Student_details' table.

 Rename Your Column may be Risky..

I got this Caution (below in Red) every time , but i got changes successfully without any data loss.

Caution: Changing any part of an object name could break scripts and stored procedures.




 -----     ---     ----     ---------------------------------------------------------------------------------------------------------
Disclaimer: In case of any loss, would not the responsibility of this blog, Data or Author.

SQL Query to get Column Count & Details of a Database

Get Columns Count of all tables in a Database

Select TABLE_NAME as 'All tables', COUNT(*) as 'no. of columns'
From INFORMATION_SCHEMA.COLUMNS
Group By TABLE_NAME
Order By TABLE_NAME

Total no. of Columns in a Table:

SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableHere'           

Columns Details of a Table: 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableHere'

All Columns Names of a Table: 

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableHere'

Create HTML table in SQL server

 EXECUTE RED CODE  TO GET OUTPUT:

DECLARE @htmlTable varchar(1000)= '<table cellpadding=0 cellspacing=0 border=0>'
SET @htmlTable += 
  CAST(
    (SELECT
      'Column1' AS td, '',
      'Column2' AS td, '',
      'right' AS [td/@align], 'Column3' AS td, ''
    FOR XML PATH('tr')) AS VARCHAR(1000)
  )

SET @htmlTable += '</table>'

SELECT @htmlTable 


OUTPUT WILL BE :

<table cellpadding="0" cellspacing="0" border="0">
            <tr>
                <td>
                    Column1
                </td>
                <td>
                    Column2
                </td>
                <td align="right">
                    Column3
                </td>
            </tr>
</table>

Replicate function in SQL server

Replicate functions is used to replicate the string of given no. of times, it takes 2 arguments, first argument takes string and second argument is an integer defines no. of times to replicate string.

REPLICATE(arg1,arg2)

arg1=> string to repeat (ex. 'ankit')
arg2=> no. of times to repeat (ex. 5)

Query: 

SELECT REPLICATE('ankit',5)


output will be: ankitankitankitankitankit

SQL function to Remove all Special Characters except the characters which you wants.

Step 1: Write Script for function

CREATE FUNCTION [dbo].[RemoveSpecialChars] (@string VARCHAR(1000)) 
RETURNS varchar(1000) 
AS 
BEGIN 

DECLARE @validValues varchar(200)='@/,.[](/) ''{}'     -- enter characters which you wants
DECLARE @validTable table(validkeywords varchar(200))
DECLARE @ids INT=1
WHILE @ids<=DATALENGTH(@validValues)
BEGIN
INSERT INTO @validTable VALUES(LTRIM(RTRIM(ASCII(SUBSTRING(@validValues,@ids,1)))))
SET @ids=@ids+1
END

DECLARE @short varchar(100)
DECLARE @index INT =1
DECLARE @str varchar(1000)=''
WHILE @index <= DATALENGTH(@string) 
BEGIN 
       SET @short=ASCII(SUBSTRING(LOWER(@string), @index, 1))
       IF (@short BETWEEN 97 AND 122) OR (@short BETWEEN 48 AND 57)  OR (@short IN (SELECT DISTINCT * FROM @validTable))
       BEGIN 
       SET @str=@str+SUBSTRING(@string, @index, 1)
       END
       SET @index =@index+1
END 
RETURN @str
END

Step 2: Execute function to create it (press F5 to execute)

Step 3: After create the function,  pass string as a parameter to get result

select dbo.RemoveSpecialChars('sds{///}\||+-()*. dsdsSWE.s@3223')


Output will be: sds{///}(). dsdsSWE.s@3223

Remove Special Characters from String or Columns in SQL server

STEP 1: Create function "GetCharacters" which removes the unwanted characters.

Parameters:
@myString contains the columns values
@validChars contains the valid chars which u want and all remains are filtered.


Create function dbo.GetCharacters(@myString varchar(max), @validChars varchar(500))
RETURNS varchar(500) AS
BEGIN
While @myString like '%[^' + @validChars + ']%'
Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')
Return @myString
END
Go


STEP 2: Create function "GetTable" which returns the filtered values in table form.


Create function GetTable(@ValidCharacters varchar(max))
RETURNS @MyTable TABLE
   (
    Result varchar(max)
   )
AS
BEGIN
INSERT INTO @MyTable (Result)
SELECT dbo.GetCharacters(address,@ValidCharacters) as Result
FROM tab_booking
RETURN

END
GO
 

Here, 
 "tab_booking" is the table.
"address" is the column which filtered.

STEP 3: Call function "GetTable"
  
 SELECT * FROM dbo.GetColumns('a-z0-9@$%')

Here 0 to 9,A to Z, @, $, % are the characters which u want.


 NOTE: *Copy the Red Code and Execute Step wise.
 *Use your own Database,tables,columns.
*execute for once step 1 & step 2 queries to create functions.
* then at last, To Get the Result execute step 3 red code any no. of times.
*if u want to make any change in the functions after create it once, then replace "Create" by "Alter" like this:
 Create function GetTable(@ValidCharacters varchar(max))

  Alter function GetTable(@ValidCharacters varchar(max))
 

Procedure to Add all rows to a single row in Sql Server

STEP 1.Create Data Base for ex. named 'udb_ankit'  (name your database acc. to you)

STEP 2.Create table (for ex. table, run this script)

USE [udb_ankit]
GO
/****** Object:  Table [dbo].[tbl_keywords]    Script Date: 07/18/2013 15:45:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_keywords](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [keyword] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_keywords] ON
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (1, N'ipod,mobile,playstation')
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (2, N'woofer,sound,mobile,ipod')
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (3, N'sound,ipod,mobile,ipod,playstation,earphone')
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (4, N'headphone')
SET IDENTITY_INSERT [dbo].[tbl_keywords] OFF


STEP 3.Create Function (which will be used by the procedure to split the fields)

USE [udb_ankit]
GO

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 07/18/2013 15:43:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))      
returns @temptable TABLE (items varchar(MAX))      
as      
begin     
    declare @idx int      
    declare @slice varchar(max)      

    select @idx = 1      
        if len(@String)<1 or @String is null  return      

    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      

        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      

        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return
end;

GO
STEP 4.Create Procedure (Run this script)

USE [udb_ankit]
GO

/****** Object:  StoredProcedure [dbo].[spInsertNewKeywords1]    Script Date: 07/18/2013 15:42:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spInsertNewKeywords1]

AS
BEGIN
declare @Values VARCHAR(MAX)=NULL
declare @temp1 table
(
    Diffkeywords varchar(max)
)

declare @temp table
(
    ALLkeywords varchar(max)
)
INSERT INTO @temp SELECT STUFF((SELECT DISTINCT  ', ' + keyword AS [text()]
FROM tbl_keywords
FOR XML PATH ('')),1,1,'')

SET @Values=(select top 1 ALLkeywords from @temp)

 INSERT INTO @temp1
    SELECT LTRIM(RTRIM(items))
    FROM [dbo].[Split] (@Values, ',')  -- call the split function
    
 select DISTINCT * from @temp1

END
GO

STEP 5. To see the result Execute Procedure; run "EXEC PROCEDURE_NAME"

For ex:

EXEC  [spInsertNewKeywords1]


------------------------------------XXXXXXXXXXXXXXXX--------------------------------

SQL function to Split the String by Delimiter and insert into a table variable

CREATE FUNCTION [dbo].[Split] (@String varchar(MAX), @Delimiter char(1))      
returns @temptable TABLE (items varchar(MAX))      
as      
begin     
    declare @idx int      
    declare @slice varchar(max)      

    select @idx = 1      
        if len(@String)<1 or @String is null  return      

    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      

        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      

        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return
end;






































USE THIS FUNCTION HERE:

SET @Values=(select top 1 ALLkeywords from @temp)

SELECT LTRIM(RTRIM(items)) FROM [dbo].[Split] (@Values, ',') 

@Values contains the keywords with commas

 

TRIM( ) is not working in sql server ??

If TRIM( ) is not working in sql server Then u can use the foll. function

" LTRIM ( RTRIM ( items ) ) "  instead  of  "TRIM( items )"

Query:
 
 SELECT LTRIM(RTRIM( field_name )  FROM Table_name

Ex :

 SELECT LTRIM ( RTRIM ( UserName )  FROM tbl_studentDetails

Create XML from DataBase Data- FOR XML PATH (' ') & STUFF()

Create XML from DataBase Data using FOR XML PATH ( )

 
RUN  select * from tbl_keywords FOR XML PATH (' ')

then u will get,

<id>1</id>
<keyword>ipod,mobile,playstation</keyword>
<id>2</id>
<keyword>woofer,sound,mobile,ipod</keyword>
<id>3</id>
<keyword>sound,ipod,mobile,ipod,playstation,earphone</keyword>
<id>4</id>
<keyword>headphone</keyword>

===============
TO GET THE RESULT IN TEXT FORM USE [text( )]

select * as [text( )] from tbl_keywords FOR XML PATH (' ')
==================

Concepts of STUFF and XML PATH( ' ' )

SELECT STUFF((SELECT keyword FROM tbl_keywords),1,1,'')   // got error

SELECT STUFF((SELECT keyword as [text()] FROM tbl_keywords  for xml path('')),1,1,'')

SELECT STUFF((SELECT ','+keyword as [text()] FROM tbl_keywords  for xml path('')),1,1,'')
=====================