SQL Server - CHARINDEX - Oracle INSTR

There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function.

In Oracle, the syntax of the INSTR function is :

instr( string1, string2 [, start_position [, nth_appearance ] ] )

String1 in the above syntax represents the string that you need to search in, whereas String2 is the substring that needs to be search for within the String1.

start_position and nth_appearance are optionals. Through start_position you can specify from which point in string1 the search should start and nth_appearance is to specify the number of occurance. By default start_position and nth_appearance is set to 1.

Now, let's check with one example.

SELECT INSTR('Rhythm of the band', 'th') FROM Dual;
SELECT INSTR('Rhythm of the band', 'th', 1, 1) FROM Dual;

The above select statements would return 4 as your output since 'th' was found to be positioned at the 4th character. But 'th' has been spelled twice in "Rhythm of the band" and if you require a second occurance from the string, then you need to change the statement like this_

SELECT INSTR('Rhythm of the band', 'th', 1, 2) FROM Dual;

The above statement however would return 11 as your output since 'th' was found at 11th position in string1.

Now, let's work on SQL Server for the same.

CHARINDEX is the alternative in SQL Server for INSTR function but it is not exactly its equivalent.

CHARINDEX ( expression1 , expression2 [ , start_location ] )

The above syntax is the same as with INSTR function except for nth_appearance. We donot have an option to set the nth_appearance.

SELECT CHARINDEX('th','Rhythm of the band')

The above statement would return 4 as your output since 'th' was found at the 4th character position.

SELECT CHARINDEX('th','Rhythm of the band', 5)
SELECT CHARINDEX('th','Rhythm of the band', 6)
SELECT CHARINDEX('th','Rhythm of the band', 7)
SELECT CHARINDEX('th','Rhythm of the band', 8)

The above statements would return 11 as your output since 'th' was found at the 11th position after the first 5 characters.

If you require an exact behaviour of INSTR function in SQL Server, then follow the link to create a user defined function.

http://www.dbforums.com/microsoft-sql-server/1101462-equivalent-oracles-instr-4-parameters-sql-server.html

SQL Server - Sample Database Link

Here is the link for Microsoft SQL Server Community Projects & Samples. They have provided the best examples and databases in the following link.

Codeplex SQLServerSamples

SQL Server - List Out Number of Rows in Database

To List-Out The Number Of Rows In Each Table From The Current Database.
To Find-Out Number Of Rows In Each Table In Database.

/****************/
--METHOD-1
/****************/
Using A Procedure_
DECLARE
@TotalRows INT,
@FirstID INT,
@table VARCHAR(255),
@cmd VARCHAR(500)

CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT)
CREATE TABLE #temp1 (col1 INT IDENTITY, TableName VARCHAR(255))

INSERT INTO #temp1
SELECT table_name FROM Information_Schema.Tables
WHERE table_type = 'base table'

SET @TotalRows = @@ROWCOUNT
SET @FirstID = 1

WHILE @FirstID <= @TotalRows BEGIN

SELECT @table = TableName FROM #temp1 WHERE col1 = @FirstIDSET @cmd = 'SELECT ''' + @table + ''', count(*) FROM ' + @tableINSERT INTO #tableData EXEC (@cmd)SET @FirstID = @FirstID + 1END

SELECT * FROM #tableData ORDER BY TotalRows DESC

DROP TABLE #tableData
DROP TABLE #temp1

CREATE TABLE #tableInfo
(
TableName VARCHAR(255),
TotalRows INT
)

INSERT #tableInfo
EXEC sp_msFOREachTable
'SELECT PARSENAME(''?'', 1),
COUNT(*) FROM ?'

SELECT * FROM #tableInfo ORDER BY TotalRows DESC

DROP TABLE #tableInfo
/****************/
--METHOD-4
/****************/
CREATE TABLE #tableInfo
(
TableName VARCHAR(255),
TotalRows INT
)

EXEC sp_msFOREachTable
'INSERT INTO #tableInfo
SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #tableInfo ORDER BY TotalRows DESC

DROP TABLE #tableInfo

/****************/
--METHOD-2
/****************/
Using A Cursor_
DECLARE @table VARCHAR(255),
@cmd VARCHAR(500)

CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT)

DECLARE cursor_tableData CURSOR FOR
SELECT table_name FROM Information_Schema.Tables
WHERE table_type = 'base table'

OPEN cursor_tableData

FETCH NEXT FROM cursor_tableData INTO @table
WHILE @@fetch_status = 0
BEGIN
SET @cmd = 'SELECT ''' + @table + ''', COUNT(*) FROM ' + @table
INSERT INTO #tableData EXEC (@cmd)
FETCH NEXT FROM cursor_tableData INTO @table
END
CLOSE cursor_tableData
DEALLOCATE cursor_tableData

SELECT * FROM #tableData ORDER BY TotalRows DESC
DROP TABLE #tableData


The following methods are using un-documented stored procedure 'sp_msFOREachTable'. There is a cursor declared in that which loops through each table in the current database and executes a script that you define.

/****************/
--METHOD-3
/****************/

SQL Server - Monthly Report - Group by Month

The following example guide you to retrieve a monthly report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a monthly report by GROUPING DATE column.

CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
DATENAME(mm, PaidDate) Month,
YEAR(PaidDate) Year,
COUNT(*) iCount,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
DATENAME(mm, PaidDate),
Year(PaidDate),
Month(PaidDate)
ORDER BY Month(PaidDate)

SQL Server - Daily Report - Group by Date

Like hourly report, the following example guide you to retrieve a daily report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a daily report by GROUPING DATE column.
CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
CONVERT(VARCHAR(10), PaidDate, 101) Day,
COUNT(*) TransactionsCount,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
CONVERT(VARCHAR(10), PaidDate, 101)

SQL Server - Hourly Report - Group by Hour

Sometimes it requires to know how many inserts are happend every hour in a transactions table OR how many payments made every hour OR to retrieve the hourly based report, whatever term it is, the requirement is to generate a report based on hours from a datetime column. If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Follow the examples.
CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
CONVERT(VARCHAR(10), PaidDate, 101) Day,
CONVERT(VARCHAR(2), PaidDate, 108) Hour,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
CONVERT(VARCHAR(10), PaidDate, 101),
CONVERT(VARCHAR(2), PaidDate, 108)

SQL Server - INSERT Statements

INSERT can be used in different ways. The following examples will guide you the method of row constructions using INSERT.
CREATE TABLE Test1(
Column1 INT,
Column2 VARCHAR(20))

INSERT INTO Test1 VALUES(1001, 'Andrews')

INTO is an optional keyword and you can omit it from the statement.
INSERT Test1 VALUES(1002, 'Mathews')

SELECT statement can also be used in row construction using INSERT
INSERT INTO Test1
SELECT 1003, 'Saarika'

INSERT INTO Test1
SELECT 1004, 'Haarika'

INSERT statement can be used using 'UNION' and 'UNION ALL'
INSERT INTO Test1
SELECT 1005, 'Saritha'
UNION
SELECT 1006, 'Haritha'
UNION
SELECT 1007, 'Vanitha'
UNION
SELECT 1008, 'Kavitha'

SELECT * FROM Test1

SQL Server - Foreign Key Relationships

To establish a connection between two or more tables, foreign key constraint will be used. One table's primary key column can be referred to another table's column as foreign key. It is to enforce a constraint to match the foreign key column data-type and values with parent key column data-type and values. The foreign key columns are used in joins while retrieving the data as the relationship between the tables indicates that the tables have been optimized to be combined.

There is no specific limitation to have number of foreign key constraints in a table, however, it is recommended to have not more than 253 foreign key constraints in a table. We can create a foreign key by defining REFERENCES or FOREIGN KEY constraint while creating or modifying a table.

Look into the following examples.

-------------
METHOD-1
-------------
CREATE TABLE studentGroups(
GroupID BIGINT PRIMARY KEY,
Groups VARCHAR(20))

CREATE TABLE student_details(
stdID BIGINT PRIMARY KEY,
student_Name VARCHAR(40) NOT NULL,
GroupID BIGINT REFERENCES studentGroups(GroupID))

-------------
METHOD-2
---------------
CREATE TABLE studentGroups(
GroupID BIGINT PRIMARY KEY,
Groups VARCHAR(20))

CREATE TABLE student_details(
stdID BIGINT PRIMARY KEY,
student_Name VARCHAR(40) NOT NULL,
GroupID BIGINT,
CONSTRAINT fk_group FOREIGN KEY(GroupID) REFERENCES studentGroups(GroupID))

------------------------------------------------------------
METHOD-3 -- ADDING FOREIGN KEY CONSTRAINT ON EXISTING TABLE
------------------------------------------------------------
CREATE TABLE studentGroups(
GroupID BIGINT PRIMARY KEY,
Groups VARCHAR(20))

CREATE TABLE student_details(
stdID BIGINT PRIMARY KEY,
student_Name VARCHAR(40) NOT NULL,
GroupID BIGINT)

ALTER TABLE student_details

WITH CHECK ADD CONSTRAINT fk_group FOREIGN KEY(GroupID)
REFERENCES studentGroups(GroupID)

SQL Server - GETDATE(), CURRENT_TIMESTAMP in DEFAULT statements

Most of the transactional tables generally do require to have a column with DEFAULT value as current date time to identify when the transaction had took place. To accomplish this, In CREATE TABLE statement you can use GETDATE() function or CURRENT_TIMESTAMP (SQL-92 niladic function) as DEFAULT. However there is no difference in output.

CREATE TABLE Employee(
empID BIGINT PRIMARY KEY,
EName VARCHAR(40) NOT NULL,
Application_Date SMALLDATETIME DEFAULT CURRENT_TIMESTAMP,
Reporting_Date SMALLDATETIME DEFAULT GETDATE(),
Relieved_Date SMALLDATETIME DEFAULT '12/31/2010', -- mmddyyyy
Address VARCHAR(20) NULL)

INSERT INTO Employee(empID, Ename, Address) Values (1001, 'Catherin', 'NewYork')

SELECT * FROM Employee

SQL Server - CASE Function- Examples

I heard many people asking about alternative for Oracle DECODE in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression.

CASE function evaluates a list of conditions and returns one of multiple possible result expressions. It has two formats:

(1) Simple Case Function
(2) Searched Case Function

The simple CASE function compares an expression to a set of simple expressions to determine the result. The searched CASE function evaluates a set of Boolean expressions to determine the result. Both formats support an optional ELSE argument.

Refer to the following examples:

------------------------------------------------
METHOD-1 - SIMPLE CASE FUNCTION
------------------------------------------------
CREATE TABLE Customers(
CustomerID INT,
CustomerName VARCHAR(30),
ActiveStatus BIT
)

INSERT INTO Customers VALUES(100, 'John', 0)
INSERT INTO Customers VALUES(200, 'Kate', 1)
INSERT INTO Customers VALUES(300, 'Julia', 1)
INSERT INTO Customers VALUES(400, 'Maddy', 0)

SELECT CustomerID,
CustomerName,
ActiveStatus = CASE ActiveStatus
WHEN 1 THEN 'Active'
ELSE 'Inactive'
END
FROM Customers
----------------------------------------------------
METHOD-2 - SEARCHED CASE FUNCTION
Case Function With Expressions
----------------------------------------------------CREATE TABLE StudentMarks(
StudentID INT,
StudentName VARCHAR(30),
Subject1 INT,
Subject2 INT,
Subject3 INT,
)

INSERT INTO StudentMarks VALUES(10, 'John', 90, 86, 79)
INSERT INTO StudentMarks VALUES(20, 'Kate', 69, 58, 43)
INSERT INTO StudentMarks VALUES(30, 'Julia', 77, 76, 87)
INSERT INTO StudentMarks VALUES(40, 'Maddy', 92, 91, 90)

SELECT * FROM StudentMarks

SELECT StudentID,
StudentName,
Subject1,
Subject2,
Subject3,
SUM(Subject1+Subject2+Subject3) TotalMarks,
Grade = CASE
WHEN SUM(Subject1+Subject2+Subject3)>250 THEN 'A'
WHEN SUM(Subject1+Subject2+Subject3) BETWEEN 200 AND 250 THEN 'B'
ELSE 'C'
END
FROM StudentMarks
GROUP BY StudentID, StudentName, Subject1, Subject2, Subject3
--------------------------------------------

Complicated Script (For an example)

--------------------------------------------
SELECT
COUNT(CASE WHEN (
ActiveStatus = 1
AND
DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) >= 1
AND
DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) <= 3 )
THEN 1 ELSE 0 END)
AS Column1,
COUNT(CASE WHEN (
ActiveStatus = 2
AND
DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) >= 4
AND
DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) <= 6 ) THEN 1 ELSE 0 END) AS Column2,
COUNT(CASE WHEN (
ActiveStatus = 3
AND
DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) >= 7
AND
DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) <= 9 ) THEN 1 ELSE 0 END) AS Column3,
FROM Table1
INNER JOIN Tabl2 On Table1.Column9 = Table2.Column9
INNER JOIN Tabl3 On Table3.Column5 = Table2.Column6