SQL Server: Check a string contains a another string

In SQL Server we can check a string or character is in another string using CHARINDEX function.
CHARINDEX function will return the index of the starting letter of the sub string in the main string. It will return 0 if no match found.
Ex:

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';  
SELECT CHARINDEX('Gopi',@string1)

Result:

We can use it in conditions as below

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF CHARINDEX('Gopi',@string1) > 0   
BEGIN 
   SELECT 'String found'
END
ELSE
BEGIN
   SELECT 'String Not found'
END

 Result:

 If string not found in the main string

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF CHARINDEX('Test',@string1) > 0   
BEGIN 
   SELECT 'String found' AS Result
END
ELSE
BEGIN
   SELECT 'String Not found' AS Result
END

 Result:

Note: We can also do it using Like operator as below. But it will not return the index of the string.

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF @string1 LIKE '%gopi%'
BEGIN 
   SELECT 'String found' AS Result
END
ELSE
BEGIN
   SELECT 'String Not found' AS Result
END

Happy Coding 😊!!

Read More»

SQL Server Case Sensitive Search - COLLATION

In SQL Server, by default the search is case insensitive, which means that it will not differentiate between upper and lower case letters. So while comparing it will treat "SQL" and "sql" as same. Technically, this is called as "collation". Default collation for the SQL server is case insensitive collation. We can use the collation in the following ways

Database level

We can add the collation at database level. So all the columns will follow this collation

 
USE master;  
GO  
 
CREATE DATABASE GopiPortal_Test  
COLLATE Latin1_General_100_CS_AS_SC;  
GO 

We can alter the collation as

 
USE master;  
GO 
 
ALTER DATABASE MyOptionsTest  
COLLATE French_CI_AS ;  
GO 

Note: Collation cannot be changed after database has been created on Azure SQL Database.

Column Level

You can override the database collation for char, varchar, text, nchar, nvarchar, and ntext data by specifying a different collation for a specific column of a table and using one of the following

 
CREATE TABLE dbo.MyTable  
  (PrimaryKey   int PRIMARY KEY,  
   CharCol      varchar(10) COLLATE French_CI_AS NOT NULL  
  );  
GO  
ALTER TABLE dbo.MyTable ALTER COLUMN CharCol  
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL;  
GO  

Query Level

In Query level also we can change it temporarily

 
SELECT TOP 1 *
FROM dbo.MyTable
WHERE UserId = @UserId COLLATE SQL_Latin1_General_CP1_CS_AS
    AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017

Read More»

Sending Email From MS SQL server using sp_send_dbmail stored procedure

Sometimes we may required to send the reports to the client via Email from SQL Jobs or we need to send Job success email to DBA once a Job is completed successfully. For such situations, MS SQL server provided a predefined stored procedure sp_send_dbmail for sending Emails from SQL directly in MSDB system database. By using this procedure we can send an email to the specified recipients. The message may include a query result set, file attachments, or both. But using this procedure needs to Configure the Database Mail in SQL Server.

Configure the Database Mail

Please follow the below steps

In the Management Studio, Go to Management -> Database Mail -> Right click on it and Select Configure Database Mail 

It will starts a wizard to configure the email server. Click on Next. 

Select "Set up Database Mail by performing the following tasks" Option and click on Next button. The remaining options are useful to edit the settings once created.

Provide the Profile name and description (optional). Now add SMTP account details by click on ADD button. We can add multiple SMTP accounts to one Profile.

It will lists the existing SMTP accounts with other Profiles. You can add existing account.

To add new account click on "New Account" button.

Enter the Name of the Account and SMTP details and click on OK button. Here I used Gmail SMTP details. Click on the Next button once you added all your SMTP accounts. 

In the next screen, select the access for your Profiles. 

* Public profiles will be accessed by all the users

* Private profiles will be accessed by some specific users only.

In the next screen, we can configure the Retry Attempts, Retry Delay, Attachment File Size and the Extensions that are allowed, Logging Level etc.

Click on OK once your configuration done and Finish in the next screen.

Once your configuration completed, you can verify it by sending the Test Email. For that, again go to Management -> Database Mail -> Right click on it and Select "Send Test E-Mail" option. Select the required profile and give the To email and click on "Send Test E-Mail" button. If your configuration is okay, you can get the email.

Sending Email using sp_send_dbmail Stored Procedure

Once your configuration completed, you can execute the sp_send_dbmail as below to send email.

EXEC [msdb].[dbo].sp_send_dbmail  
    @profile_name = 'My Test Profile',  
    @recipients = 'yourname@gmail.com',  
    @body = 'This mail is generated from SQL Proc.',  
    @subject = 'This is SQL Mail test' ;

Following query will send the Query results as attachment in the email. 

EXEC [msdb].[dbo].sp_send_dbmail 
 @profile_name='My Test Profile',
 @recipients='yourname@gmail.com',
 @subject='Test Report from SQL',
 @body='Hello, Please find the attachment. Thank you.',
 @query= 'SET NOCOUNT ON;SELECT * FROM [Student].[dbo].test2',
 @attach_query_result_as_file=1,
 @query_attachment_filename = 'Test.csv',
 @query_result_separator = ','

Following will send the body as HTML

EXEC [msdb].[dbo].sp_send_dbmail 
 @profile_name='My Test Profile',
 @recipients='yourname@gmail.com',
 @subject='Test Report from SQL',
 @body='Hello,<br/><h2>Please find the attached Details</h2><br/><br/>Thank you<br/><b>Administrator</b>',
 @body_format='html',
 @query= 'SET NOCOUNT ON;SELECT * FROM [Student].[dbo].test2',
 @attach_query_result_as_file=1,
 @query_attachment_filename = 'Test.csv',
 @query_result_separator = ','

You can find all the parameter which can be used with sp_send_dbmail here.
Happy Coding! 😊

Read More»

SQL Server: Get Column Names based on the Record's Value

Scenario:

I have a table with below Schema

Name	Column1	Column2	Column3	Column4
Gopi	1	1	0	1
Krishna	0	1	1	1

and I want the output in the following format

Name	ColumnsSelected
Gopi	Column1, Column2, Column4,
Krishna	Column2, Column3, Column4,

Solution:

I achieved the above using CROSS APPLY

If the column names are static

SELECT Name,ColumnsSelected=(
SELECT cname+','
FROM   TestTable B
CROSS apply (VALUES('Column1',Column1),
                    ('Column2',Column2),
                    ('Column3',Column3),
                    ('Column4',Column4)) ca (cname, data)
WHERE  data = 1 AND A.Name=B.Name
FOR XML PATH('')) 
FROM TestTable A
Group by name

If the column names are dynamic

DECLARE @collist VARCHAR(MAX)='',
        @sql     NVARCHAR(MAX);
  
SELECT @collist += '(''' + COLUMN_NAME + ''',' + COLUMN_NAME + '),'
FROM   INFORMATION_SCHEMA.columns
WHERE  TABLE_NAME = 'TestTable'
  AND COLUMN_NAME NOT IN ('id','Name')
  AND TABLE_SCHEMA = 'dbo';

SELECT @collist = LEFT(@collist, Len(@collist) - 1);

SET @sql ='
SELECT Name,ColumnsSelected=(
SELECT cname+'', ''
FROM   TestTable B
    CROSS APPLY (VALUES' + @collist
    + ') ca (cname, data)
WHERE data=1 AND A.Name=B.Name
FOR XML PATH(''''))
FROM TestTable A
GROUP BY A.Name ';

EXEC Sp_executesql  @sql;

Happy Coding! 😊

Credits: Stack overflow question

Read More»

Efficient way to Convert Seconds in to hours,minutes and Seconds in Sql Server

Suppose you have a column contains time in seconds, and need to convert those seconds into time format (hh:MM:ss), following is the best method for doing this.


SELECT CONVERT(CHAR(8),DATEADD(SECOND,,0),108);

Ex: Suppose, you want to convert 105 seconds in to time format, then


SELECT CONVERT(CHAR(8),DATEADD(SECOND,105,0),108);


will returns

00:01:45

If you want only minutes and seconds, then you can get it with the below query


SELECT RIGHT(CONVERT(CHAR(8),DATEADD(SECOND,105,0),108),5);

will returns

01:45

It is the  fastest solution of all the other solutions.

Read More»

SQL Server - Determine How much disk space does database table consume

Sometimes we needs to calculate the disk space used by the each table in the database to know the highest weight table in the database. Following are the different methods for achieve this.

Method 1:
SQL Server provides a special procedure called  "sp_spaceused". Using this, we can get the space used by a table as below.

EXEC sp_spaceused  <Table Name>

To get the all the tables data we can use the following command

EXEC sp_msforeachtable 'sp_spaceused "?"'

The ? serves as a placeholder for each table name.
It is the fastest method.

Method 2:
Below query will give all the tables in highest used space wise

SELECT T.NAME AS TableName
,S.Name AS SchemaName
,P.Rows AS RowCounts
,SUM(A.total_pages) * 8 AS TotalSpaceKB
,SUM(A.used_pages) * 8 AS UsedSpaceKB
,(SUM(A.total_pages) - SUM(A.used_pages)) * 8 AS UnusedSpaceKB
FROM SYS.TABLES T
INNER JOIN SYS.INDEXES I ON T.object_id = I.object_id 
INNER JOIN SYS.PARTITIONS P ON I.object_id = P.object_id AND I.index_id = P.index_id
INNER JOIN SYS.ALLOCATION_UNITS A ON P.partition_id = A.container_id
LEFT OUTER JOIN SYS.SCHEMAS S ON T.schema_id = S.schema_id 
WHERE T.NAME NOT LIKE 'dt%'
    AND T.is_ms_shipped = 0
    AND I.object_id > 255
GROUP BY T.Name, S.Name, P.Rows
ORDER BY UsedSpaceKB DESC

Read More»

Sql Server Synonyms

According to Microsoft, A synonym is a database object that serves the following purposes.

• Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
• Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

For example, consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.

To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table.

Note : There is no ALTER SYNONYM statement, you first have to drop the synonym, and then re-create the synonym with the same name with the new changes.

In General words,A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Synonyms can be created on the following objects only.

1. Assembly (CLR) Stored Procedure
2. Assembly (CLR) Table-valued Function
3. Assembly (CLR) Scalar Function
4. Assembly Aggregate (CLR) Aggregate Functions
5. Replication-filter-procedure
6. Extended Stored Procedure
7. SQL Scalar Function
8. SQL Table-valued Function
9. SQL Inline-table-valued Function
10. SQL Stored Procedure
11. View
12. Table (User-defined)  (Includes local and global temporary tables)

Create Synonyms :

USE AdventureWorks;

GO

CREATE SYNONYM MyProduct

FOR AdventureWorks2012.Production.Product;

GO

Use Synonyms :

USE AdventureWorks;

GO

SELECT TOP 5 *

FROM MyProduct;

GO

Drop Synonyms :

USE AdventureWorks;

GO

DROP SYNONYM MyProduct;

GO

SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

You can GRANT, DENY, REVOKE all or any of the following permissions on a synonym:

1. CONTROL
2. DELETE
3. EXECUTE
4. INSERT
5. SELECT
6. TAKE OWNERSHIP
7. UPDATE
8. VIEW DEFINITION

Reference:

http://msdn.microsoft.com/en-us/library/ms191230(v=SQL.90).aspx

http://msdn.microsoft.com/en-IN/library/ms177544.aspx

Read More»

SQL Function to remove HTML tags from Varchar field

Following is the function to remove HTML tags from a varchar field in SQL Server


CREATE FUNCTION [dbo].[RemoveHTML]
(
@vHTMLText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @iFirst INT
    DECLARE @iLast INT
    DECLARE @iLength INT
    SET @iFirst = CHARINDEX('<',@vHTMLText)
    SET @iLast = CHARINDEX('>',@vHTMLText,CHARINDEX('<', @vHTMLText))
    SET @iLength = (@iLast - @iFirst) + 1
    WHILE @iFirst > 0 AND @iLast > 0 AND @iLength > 0
    BEGIN
        SET @vHTMLText = STUFF(@vHTMLText,@iFirst,@iLength,'')
        SET @iFirst =CHARINDEX('<',@vHTMLText)
        SET @iLast =CHARINDEX('>' ,@vHTMLText, CHARINDEX('<', @vHTMLText))
        SET @iLength = (@iLast - @iFirst) + 1
    END
    RETURN LTRIM(RTRIM(@vHTMLText))
END

Read More»

Search in All Stored Procedures of a Database

Some times to search for all the Stored Procedures having a given text in its definition. Then we can query like below to get the results

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchWord%'

Read More»

Restore a Database from the SQL Command

Assume your Database "SampleDB" has full backup SampleDB.bak. It can be restored using following steps. Run this queries in the maste database.

1) Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'E:SampleDBBackupfile.bak'

2) Use the values in the LogicalName Column in following Step.

-- Make Database to single user Mode
ALTER DATABASE SampleDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

-- Restore Database
RESTORE DATABASE SampleDB
FROM DISK = 'E:SampleDBBackupfile.bak'
WITH MOVE 'MDFLogicalName' TO 'D:SampleDBDataFile.mdf',
MOVE 'LDFLogicalName' TO 'D:SampleDBLDFFile.ldf',
REPLCE

/* Please run below statement if any error occures in the above Statement */
-- Convert Database again to Multi User Mode
ALTER DATABASE SampleDB SET MULTI_USER

In the above RESTORE Statement 'MDFLogicalName' and 'LDFLogicalName' are the logical names of the backup file which can be retrived from Step 1.

'D:SampleDBDataFile.mdf' and 'D:SampleDBLDFFile.ldf' are the physical File paths for the SampleDB.

Reference : blog.sqlauthority.com

Read More»

Generating Comma Seperated List Using SELECT Clause From Table Column

Following script will Generate comma separate values or comma separate list from tables.


Method 1):
DECLARE @CSV VARCHAR(MAX)
SELECT @CSV= COALESCE(@CSV+ ',', '') + < Column Name >
FROM <TableName >
WHERE <Condition >
SELECT @CSV


Method 2) :
DECLARE @Result AS VARCHAR(2000)
SET @Result = ''
SELECT @Result = < Column Name >+', '+ @Result
FROM <TableName >
WHERE <Condition >

SET @Result=SUBSTRING(@Result,0,LEN(@Result))
SELECT @Result


Example:
Below query gives "CourseName" as a table

SELECT CourseName
FROM Courses

Output:

CourseName
------------
ASP.NET
SQL Server 2005
Java
MVC

If you give the following query, it will returns the data as a comma seperated values

DECLARE @CSV VARCHAR(MAX)
SELECT @CSV= COALESCE(@CSV+ ',', '') + CourseName
FROM Courses
SELECT @CSV

Output:
ASP.NET,SQL Server 2005,Java,MVC


In the above methods First method is the efficient one. Because

COALESCE function deals with the null values.

COALESCE() :
According to MSDN, coalesce returns the first non-null expression among its arguments.
COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value

Read More»

SQL Server Date Time Conversion Formats

Below are the basic formats for converting the date time values into varchar in SQL Server 2005

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
-- Aug 2 2010 11:01AM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 08/02/2010
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.08.02
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
-- Aug 2 2010 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd


SELECT convert(varchar, getdate(), 112) -- yyyymmdd ( ISO date format ,international standard - works with any language setting)
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
-- 02 Aug 2010 11:02:07:577
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
-- 2010-08-02T10:52:47.513


DateTime conversions -  Without century (YY) 


SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1) -- mm/dd/yy
SELECT convert(varchar, getdate(), 2) -- yy.mm.dd
SELECT convert(varchar, getdate(), 3) -- dd/mm/yy
SELECT convert(varchar, getdate(), 4) -- dd.mm.yy
SELECT convert(varchar, getdate(), 5) -- dd-mm-yy
SELECT convert(varchar, getdate(), 6) -- dd mon yy
SELECT convert(varchar, getdate(), 7) -- mon dd, yy
SELECT convert(varchar, getdate(), 8) -- hh:mm:ss
SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10) -- mm-dd-yy
SELECT convert(varchar, getdate(), 11) -- yy/mm/dd
SELECT convert(varchar, getdate(), 12) -- yymmdd
SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) -- hh:mm:ss
SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm


Different date styles with SQL string functions


SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm

Read More»