Match Case sensitive String in SQL Server

How do you compare strings so that the comparison is true only if the cases of each of the strings are equal as well. For example:

Select * from a_table where attribute = 'a'

...will return a row with an attribute of 'A'. I do not want this behavior.

So, we can write.............

Select * from a_table where attribute = 'a' COLLATE Latin1_General_CS_AS

You can also convert that attribute as case sensitive using this syntax :

ALTER TABLE Table1

ALTER COLUMN Column1 VARCHAR(200)

COLLATE SQL_Latin1_General_CP1_CS_AS

Now your search will be case sensitive.

If you want to make that column case insensitive again, then use

ALTER TABLE Table1

ALTER COLUMN Column1 VARCHAR(200)

COLLATE SQL_Latin1_General_CP1_CI_AS

How to install SQL Server 2008 R2

You may Install SQL Server 2008 R2 using help of these sites...

http://www.google.com

http://blogs.msdn.com/b/petersad/archive/2009/11/13/how-to-install-sql-server-2008-r2-express-edition-november-ctp.aspx

http://www.sqlservergeeks.com/articles/sql-server-bi/32/installation-guide-for-sql-server-2008-r2

http://www.sqlcoffee.com/SQLServer2008_0013.htm

Which site I follow...

I follow these sites for VB.Net & SQL Server. You may also follow these...

http://blog.sqlauthority.com/
http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=181
http://www.katieandemil.com/bi-tutorial-from-beginner-to-expert-microsoft-products
http://ssrstutorials.blogspot.in/2012/10/lesson-19-sqlssrs-interview-questions.html
http://www.programmerinterview.com/
http://www.mssqltips.com/
http://dot-nettuts.blogspot.in/
http://dotnet01.blogspot.in/
http://technet.microsoft.com/en-us/library/
http://sqltutorials.blogspot.in/
http://www.visual-basic-tutorials.com/
https://flipboard.com/section/everything-about-data-by-sqlauthority-burkRy
http://sqltutorials.blogspot.in/
http://sqlguru.in/#
http://blogs.msdn.com/
http://www.sqlcoffee.com/index.htm
http://www.sqlservergeeks.com/articles/

Dynamic Pivot in SQL Server

USE tempdb

GO

CREATE TABLE RESULT

( SID INT,

SUB VARCHAR(10),

MARKS INT

)

GO

INSERT INTO RESULT

SELECT 1,'SUB1',54 UNION ALL SELECT 1,'SUB2',46 UNION ALL SELECT 1,'SUB3',57 UNION ALL SELECT 1,'SUB4',86 UNION ALL

SELECT 2,'SUB1',64 UNION ALL SELECT 2,'SUB3',34 UNION ALL SELECT 2,'SUB5',76 UNION ALL SELECT 2,'SUB6',96 UNION ALL

SELECT 3,'SUB2',54 UNION ALL SELECT 3,'SUB4',46 UNION ALL SELECT 3,'SUB5',57 UNION ALL SELECT 3,'SUB6',86 UNION ALL

SELECT 4,'SUB1',64 UNION ALL SELECT 4,'SUB3',34 UNION ALL SELECT 4,'SUB4',76 UNION ALL SELECT 4,'SUB6',96

GO

SELECT * FROM RESULT

GO

-- NORMAL PIVOT TABLE --

SELECT * FROM RESULT

PIVOT (MAX(MARKS) FOR SUB

IN (SUB1,SUB2,SUB3,SUB4,SUB5,SUB6))AS PVT

WHERE SID = 1

SID SUB1 SUB2 SUB3 SUB4 SUB5 SUB6

1 54 46 57 86 NULL NULL

-- DYNAMIC PIVOT TABLE USING STUFF()—-

DECLARE @SID VARCHAR(8)

DECLARE @QRY VARCHAR(MAX)

SET @SID = '1'

SET @QRY = (SELECT DISTINCT STUFF(( SELECT ', ' + CAST(SUB AS VARCHAR(MAX)) FROM RESULT WHERE (SID = @SID)

                  FOR XML PATH ('')),1,2,'') AS SUB

                  FROM RESULT)

SELECT @QRY = 'SELECT * FROM

                  (

                  SELECT * FROM RESULT WHERE SID = '+@SID+'

                  ) V1

                  PIVOT (MAX(MARKS) FOR SUB IN

                  ('+@QRY+')

                  )AS PVT'

EXECUTE(@QRY)

SID SUB1 SUB2 SUB3 SUB4

1 54 46 57 86

-- DYNAMIC PIVOT USING COALESCE() --

DECLARE @SID VARCHAR(8)

DECLARE @QRY1 VARCHAR(MAX)

SET @SID = '1'

SELECT @QRY1 = COALESCE(@QRY1 +',','') + SUB FROM

                  (SELECT SUB FROM RESULT WHERE SID = @SID) AS X

                 

SELECT @QRY1 = 'SELECT * FROM

                  (

                  SELECT * FROM RESULT WHERE SID = '+ @SID +'

                  ) V1

                  PIVOT (MAX(MARKS) FOR SUB IN

                  ('+ @QRY1 +')

                  )AS PVT'

                 

EXECUTE(@QRY1)

SID SUB1 SUB2 SUB3 SUB4

1 54 46 57 86

DROP TABLE RESULT

Hope, you are understand. Thank You...

Date Difference in Microsoft Excel

Here, Cell A2 = From Date 

and B2 = To Date

Now, Type this Formula in Date Difference Field

= DATEDIF (A2,B2,"Y")&" Years, "&DATEDIF(A2,B2,"YM")&" Months, "&DATEDIF(A2,B2,"MD")&" Days"

Date Difference in Microsoft Excel

How to Remove Extra Space from a String in SQL Server

DECLARE @NAME AS VARCHAR(100)

SET @NAME = 'Arka      G      Gupta'

SELECT @NAME AS [String with extra spaces],

REPLACE(REPLACE(REPLACE(@NAME,CHAR(32),'()'),')(',''),'()',CHAR(32))

AS [String without extra spaces]

OUTPUT--

String with extra spaces	String without extra spaces
Arka      G      Gupta	Arka G Gupta

Log File in VB.Net

Saved Log File

Imports System.IO.StreamWriter

Private Sub log_file()

        Try

            Dim s As IO.StreamWriter

            Dim portfolioPath As String = My.Application.Info.DirectoryPath

            If Not IO.Directory.Exists("C:\Users\Arka\Desktop\Arka_emp") Then

                IO.Directory.CreateDirectory("C:\Users\Arka\Desktop\Arka_emp")

                IO.File.Create("C:\Program Files\log.txt").Close()

                s = New IO.StreamWriter("C:\Program Files\log.txt", True)

                s.WriteLine(Date.Now & " Insert ERROR in Name " & dt.Rows(x)(1))

                s.Flush()

                s.Close()

            Else

                s = New IO.StreamWriter("C:\Program Files\log.txt", True)

                s.WriteLine(Date.Now & " Insert ERROR in Name " & dt.Rows(x)(1))

                s.Flush()

                s.Close()

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

Runtime Log File

Private Sub log_file()

        System.Diagnostics.Process.Start("notepad")

        Dim found As Boolean = False

        Do Until found = True

            System.Threading.Thread.Sleep(100)

            For Each p As Process In Process.GetProcesses

                If p.ProcessName = "notepad" Then

                    found = True

                End If

            Next

        Loop

        SendKeys.Send("Whatever you want to say..." & Environment.NewLine & "And you can make new lines too!")

    End Sub

Importing data from csv to sql server using vb.net

Imports System

Imports System.IO.File

Dim dlg As New OpenFileDialog

Dim ocon As System.Data.OleDb.OleDbConnection

Dim ocmd As System.Data.OleDb.OleDbDataAdapter

Dim ds As System.Data.DataSet

Dim path As String = "My Filename"

Dim path1 As String

Dim dt As New DataTable

Private Sub Upload_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Upload_btn.Click

        Dim dt As New DataTable

        dlg.Filter = "CSV Fiels|*.csv|Any Files|*.*"

        If dlg.ShowDialog = System.Windows.Forms.DialogResult.OK Then

            path = dlg.FileName

            path1 = dlg.FileName

            path = System.IO.Path.GetDirectoryName(path)

            path1 = System.IO.Path.GetFileName(path1)

            ocon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties='Text;HDR=Yes;IMEX=1;FMT=Delimited(,)';")

            ocmd = New System.Data.OleDb.OleDbDataAdapter("select * from " & path1 & "", ocon)

            ocmd.Fill(dt)

            ocon.Close()

***********************FOR EXCEL PURPOSE**************************

'Imports Microsoft.Office.Interop

'Imports Microsoft.Office.Interop.Excel

            'ocon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data source='" & path & "';" & "Extended Properties= Excel 12.0;")

            'ocmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", ocon)

            'ds = New System.Data.DataSet

            'ocmd.Fill(ds)

            'ocon.Close()

            'dt = ds.Tables(0)

*********************************************************************************

        End If

        Try

            Dim x As Integer

            i = dt.Rows.Count

            While (x <= i - 1)

                Dim a As String = dt.Rows(x)(3)

                Dim dat As String = a.Replace("'", "")

                dat = Mid(dat, 5, 4) + Mid(dat, 3, 2) + Mid(dat, 1, 2)

                sql = "Insert into ABCD values(@id, @name, @add, @dob)"

                cmd = New SqlCommand(sql, con)

                cmd.Parameters.Add(New SqlClient.SqlParameter("@id", SqlDbType.Int)).Value = dt.Rows(x)(0)

                cmd.Parameters.Add(New SqlClient.SqlParameter("@name", SqlDbType.VarChar)).Value = dt.Rows(x)(1)

                cmd.Parameters.Add(New SqlClient.SqlParameter("@add", SqlDbType.VarChar)).Value = dt.Rows(x)(2)

                cmd.Parameters.Add(New SqlClient.SqlParameter("@dob", SqlDbType.VarChar)).Value = dat

                cmd.ExecuteNonQuery()

                x = x + 1

            End While

            MsgBox("Insert Data Successful", MsgBoxStyle.Information, "Data Upload")

            dt = Nothing

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

Replicate in sql server 2008

SELECT 'Ha'+REPLICATE('ha',20)

--    OUTPUT Hahahahahahahahahahahahahahahahahahahahaha

DECLARE @FirstString VARCHAR(MAX)

SET @FirstString = REPLICATE('A',4000)

SELECT LEN(@FirstString) LenFirstString;

--    OUTPUT 4000

DECLARE @SecondString VARCHAR(MAX)

SET @SecondString = REPLICATE('B',8000)

SELECT LEN(@SecondString) LenSecondString

--    OUTPUT 8000

DECLARE @ThirdString VARCHAR(MAX)

SET @ThirdString = REPLICATE('C',10000)

SELECT LEN(@ThirdString) LenThirdString

--    OUTPUT 8000 (HERE VALUE SHOULD BE 10000)

DECLARE @ThirdString VARCHAR(MAX)

DECLARE @ThirdSource VARCHAR(MAX)

SET @ThirdSource = 'C'

SET @ThirdString = REPLICATE(@ThirdSource,10000)

SELECT LEN(@ThirdString) LenThirdString

--    OUTPUT 10000

DECLARE @ThirdString VARCHAR(MAX)

SET @ThirdString = REPLICATE(CAST('C' AS VARCHAR(MAX)),11000)

SELECT LEN(@ThirdString) LenThirdString;

--    OUTPUT 11000

QUOTED_IDENTIFIER AND ANSI_NULLS IN SQL SERVER

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?

Below is the typical usage of these options.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE SampleProcedure

AS

BEGIN

 -- select employees

 SELECT * FROM HumanResources.Employee

END

Lets first understand what they exactly mean to SQL Server and then we will move on to why they are special.

SET QUOTED_IDENTIFIER ON/OFF:

It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes. When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a literal.

SET QUOTED_IDENTIFIER ON

CREATE TABLE "SELECT" ("TABLE" int)  -- SUCCESS

GO

SET QUOTED_IDENTIFIER ON

SELECT "sometext" AS Value   -- FAIL because “sometext” is not a literal

Though the “SELECT” and “TABLE” are reserved keywords  we are able to create the table because they are now treated as identifiers and the T SQL rules for identifier names are ignored.

When it is set to OFF any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.

SET QUOTED_IDENTIFIER OFF

CREATE TABLE "SELECT"(“TABLE” int) -- FAIL

GO

SET QUOTED_IDENTIFIER OFF

SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated as a literal

You can clearly see the difference in CREATE TABLE and SELECT query. Here the CREATE TABLE fails because “SELECT” is a reserved keyword and it is considered as a literal. The default behavior is ON in any database.

 SET ANSI_NULLS ON/OFF:

The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL. And when it is set to OFF any comparison with NULL using = and <> will work as usual i.e. NULL = NULL returns true and 1= NULL returns false.

SET ANSI_NULLS ON
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  different

SET ANSI_NULLS ON
IF NULL IS NULL
 PRINT 'same'
ELSE
 PRINT 'different'
-- result: same

SET ANSI_NULLS OFF
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  same (now NULL = NULL works as 1=1)

The default behavior is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

 Why are these two options Special?:

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET; SQL Server remembers those settings in the associated object metadata. And every time the object (stored procedure,Trigger..etc.) is executed SQL server uses the stored settings irrespective of what the current user session settings are. So the behavior of the stored procedure is not altered by the calling session settings and the usage of the SET option behavior inside the SP is always guaranteed.

You can get any procedure or trigger or function settings for these options from the sys..sql_modules metadata table.

SELECT uses_ansi_nulls, uses_quoted_identifier
 FROM sys.sql_modules WHERE object_id = object_id('SampleProcedure')

And if you need to guarantee the behavior for other SET options like SET ARITHABORT inside the SP then you need to SET them inside the procedure. The scope of the options specified inside the procedure are only applicable until the procedure completes its execution.

 Hope it helps.

- Arka Gupta