Feeds:
Posts
Comments

Archive for December, 2012

SQL SERVER – Function to Extract Numbers From String

Posted in Functions, SQL SERVER, tagged , , , on December 19, 2012| 1 Comment »

I had to migrate data from old system to new system and I found that we have one field called ‘comments’ having contact details of the customers. The next stage was to extract the numbers (Contact details) from string (Comments column).

We can develop this solution via cursor as well but I developed it without cursor.

Given below is the solution.

CREATE FUNCTION dbo.[UDF_Extract_Numbers_From_String]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND  PATINDEX('%[0-9.+-]%',SUBSTRING(@String,Nums.n,1))>0

RETURN @RETURN_STRING
END
GO

SELECT dbo.UDF_Extract_Numbers_From_String
('Mobile No +49 4879 17835 is activated') as [Numbers]
GO
SELECT dbo.UDF_Extract_Numbers_From_String
('New Fax No is +2-213-8764243') as [Numbers]

--RESULT

Read Full Post »

SQL SERVER – Create Comma Separated List From Table

Posted in Solutions, SQL SERVER, tagged , , , , , , on December 18, 2012| 24 Comments »

I was preparing a statistical report and was stuck in one place where I needed to convert certain rows to comma separated values and put into a single row.

Lets say I have multiple currencies in a table and I would like to display in a single comma separated rows.

I found few options to resolve this.

  1. USING CURSOR
  2. USING COALESCE
  3. USING STUFF

Let me create a simple example to explain these methods in detail.

Create Table tbl_Currency
(
[Currency] varchar(50)
)
GO
Insert into tbl_Currency Values ('US dollar')
Insert into tbl_Currency Values ('European euro')
Insert into tbl_Currency Values ('Indian rupee')
Insert into tbl_Currency Values ('Pakistani rupee')
Insert into tbl_Currency Values ('Philippine peso')
Insert into tbl_Currency Values ('UAE dirham')
Insert into tbl_Currency Values ('British pound')

Let me explain these methods in detail :

    • USING CURSOR:

This is a simple way for development but performance wise it will cost a lot for your application.

Given below is a CURSOR example :

CREATE PROCEDURE Cursor_Example1_Proc
AS
DECLARE @Currency varchar(Max)
DECLARE @Consolidated_Currency varchar(Max)
DECLARE Cur_Cursor CURSOR FOR
SELECT [Currency] FROM tbl_Currency

OPEN Cur_Cursor

FETCH NEXT FROM Cur_Cursor INTO @Currency

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Consolidated_Currency =ISNULL(@Consolidated_Currency,'')
+ ISNULL(@Currency + ', ','')

FETCH NEXT FROM Cur_Cursor INTO  @Currency
END
Select Left(@Consolidated_Currency,LEN(@Consolidated_Currency)-1) as [Currency]

CLOSE Cur_Cursor
DEALLOCATE Cur_Cursor
GO
EXEC Cursor_Example1_Proc
GO
    • USING COALESCE:

We can also achieve the same using COALESCE function. Given below is an example.

Declare @Currency varchar(Max)
Set @Currency=''
Select @Currency=@Currency + Coalesce([Currency]+ ', ','') from tbl_Currency
Select Left(@Currency,LEN(@Currency)-1) as [Currency]
GO
    • USING STUFF:

This is the recommended / best way to do this because you can achieve the same result without any variable and less lines of code. Given below is an example.

SELECT STUFF((SELECT ', ' + [Currency] FROM tbl_Currency FOR XML PATH('')),1,1,'') as [Currency]
GO

RESULT :
The result of all of the above methods is the same. Given below is the result.

  • REAL WORLD EXAMPLE :

As mentioned above, I was working on a report where multiple Bank A/C # should be displayed next to the customer name like a comma separated field.

Let me create an example to explain this :

Create Table tbl_Customer_Bank_Details
(
[Customer ID] int,
[Customer Name] varchar(50),
[Bank Account No] varchar(50)
)
GO
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A001')
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A002')
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A003')
Insert into tbl_Customer_Bank_Details Values (2,'Bob','B001')
Insert into tbl_Customer_Bank_Details Values (2,'Bob','B002')
GO
Select * from tbl_Customer_Bank_Details
--RESULT

Lets combine the account number (comma separated values) with respect to the customer.

SELECT [Customer ID], [Customer Name]
, STUFF((SELECT ', ' + A.[Bank Account No] FROM tbl_Customer_Bank_Details A
Where A.[Customer ID]=B.[Customer ID] FOR XML PATH('')),1,1,'') As [Bank Accounts]
From tbl_Customer_Bank_Details B
Group By [Customer ID], [Customer Name]

Read Full Post »

SQL SERVER – Proper Case User-Defined Function

Posted in Functions, SQL SERVER, tagged , , , , , , on December 13, 2012| 3 Comments »

Today, I was cleaning data for upload in the live database. I found few discrepancies in customer names. The names did not have a standard pattern, some of them in proper case, while few others in small / upper case and also some in sentence case.

However, the client wanted these names to be converted to Proper Case. As usual, I started the easy way… the web search. But almost all solutions use loop to convert it into PROPER CASE.

I normally avoid loop to perform any operation until unless it is the last option due to performance issue.

So, I designed the solution via Xquery and converted the string to Proper Case without loop.

Let me create sample to explain it.

Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)

Go
Insert into Student Values ('Steve Masters')
Insert into Student Values ('david ortiz')
Insert into Student Values ('Michael sean ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('bjorn rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('carOL Philips')
Insert into Student Values ('Merav Netz')

GO
Create Function dbo.[UDF_PROPER_CASE]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS varchar(MAX)
BEGIN
Declare @Xml XML
Declare @ProperCase Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=' '
SET @Xml = cast(('<A>'+replace(@String,@delimiter,'</A><A>')+'</A>') AS XML)

;With CTE AS (SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('A') AS FN(A) )
Select @ProperCase =Stuff((Select ' ' + UPPER(LEFT([Column],1))
+ LOWER(SUBSTRING([Column], 2 ,LEN([Column]))) from CTE
for xml path('') ),1,1,'')

RETURN (@ProperCase)
END
GO
-- For Example :
SELECT dbo.[UDF_PROPER_CASE]([Student Name]) as [Student Name]
from Student
GO

Read Full Post »

SQL SERVER – All Table & its Index sizes in MBs

Posted in Solutions, SQL SERVER, tagged , , , , on December 12, 2012| 4 Comments »

Today, I received a query enquiring how to calculate the table Size, its index size along with the total number of rows, as the client’s database file size suddenly grew from MBs to GBs and they wanted to know what was inserted in which tables that caused this instant growth.

So, I started searching for solution over the web, but most of the solutions use cursor to calculate each table & its index size along with the total number of rows. I could not get what I was looking for, so, I thought of writing this and calculate it with an optimized query and convert the table & its index size to MBs instead of KBs because windows keep the file sizes in MBs also.

Given below is the Script :

Declare @PageSize as int
Select @PageSize= low/1024.0 from master.dbo.spt_values Where Number=1 And type='E'
select object_name(i.object_id) as [Table Name]
, Convert(numeric(18,3),Convert(numeric,@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1
THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024)
As [Data Space Used (In Mbs)]

,Convert(numeric(18,3), Convert(numeric(18,3),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024)
As [Index Space Used  (In Mbs)]
, SUM(Case When p.index_id=1 and a.type=1 Then p.rows else 0 end)
As [Total No of Rows]

FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
LEFT Join sys.tables t ON i.object_id=t.object_id

Where t.type='U'
Group By object_name(i.object_id)
Order By object_name(i.object_id)

Read Full Post »

SQL Server 2012 – Analytic Functions – CUME_DIST

Posted in Functions, SQL SERVER, tagged , , , , on December 10, 2012| 1 Comment »

CUME_DIST is a very nice & helpful analytical function introduced in SQL SERVER 2012. In this article, we will discuss its syntax, purpose, return type, simple examples and real world examples also.

syntax 

CUME_DIST( )
OVER ( [ partition_by_clause ] order_by_clause )

purpose
The purpose of this function is to calculate the cumulative distribution of value in a group of values.

Return Type
The return type is float(53) and the values are always between 0 and 1.

Example 1 :  Simple CUME_DIST ()

Lets take gold rates as an example to check their cumulative distribution in one week.

Create table [Daily_Gold_Rate]
(
(
[S.No] int,
[Date] datetime,
[Carat] int,
[Gold Rate] numeric(18,2)
)

Insert into [Daily_Gold_Rate] values(1,'2012-12-03',18,155.00)
Insert into [Daily_Gold_Rate] values(4,'2012-12-04',18,153.00)
Insert into [Daily_Gold_Rate] values(7,'2012-12-05',18,152.00)
Insert into [Daily_Gold_Rate] values(10,'2012-12-06',18,154.50)
Insert into [Daily_Gold_Rate] values(13,'2012-12-07',18,154.50)

GO

Select
Row_Number() OVER (ORDER BY [Carat],[Gold Rate]) as [Row Number]
,[Date]
,[Carat]
,[Gold Rate]
,CUME_DIST () OVER (ORDER BY [Carat],[Gold Rate]) AS [CUME_DIST]
from [Daily_Gold_Rate]

Explanation :


If you look at Column E and Column H, Column E is calculated by SQL Server and Column H is calculated manually to understand how it works. To calculate CUME_DIST manually, you need two values.

1. Row Number based on the values (Meaning less than or equal to value) (Column F)
2. Total Number of Records (Column G)

Column G is simple; you need to calculate the total number of records.
Column F: You simply need to get the row number based on the values. You can observe that it is simple row number till row number 2 but in row number 3 & 4 we found the same Gold rate, so it picked up 4 (due to less than or equal to value criteria) as row number.

Finally, you need to divide Column F by Column G to get the CUME_DIST manually. The same functionality CUME_DIST does automatically in SQL.

Example 2 : CUME_DIST () with Partition By Clause

Lets insert other Gold rates to proceed with this example.

Insert into [Daily_Gold_Rate] values(2,'2012-12-03',22,190.50)
Insert into [Daily_Gold_Rate] values(3,'2012-12-03',24,202.23)

Insert into [Daily_Gold_Rate] values(5,'2012-12-04',22,191.00)
Insert into [Daily_Gold_Rate] values(6,'2012-12-04',24,202.25)

Insert into [Daily_Gold_Rate] values(8,'2012-12-05',22,190.00)
Insert into [Daily_Gold_Rate] values(9,'2012-12-05',24,203.25)

Insert into [Daily_Gold_Rate] values(11,'2012-12-06',22,189.50)
Insert into [Daily_Gold_Rate] values(12,'2012-12-06',24,201.50)

Insert into [Daily_Gold_Rate] values(14,'2012-12-07',22,189.00)
Insert into [Daily_Gold_Rate] values(15,'2012-12-07',24,201.00)

Select
Row_Number() OVER (Partition by [Carat] ORDER BY [Carat],[Gold Rate])
as [Row Number]
,[Date]
,[Carat]
,[Gold Rate]
,CUME_DIST () OVER (Partition by [Carat] ORDER BY [Carat],[Gold Rate])
AS [CUME_DIST]
from [Daily_Gold_Rate]

Reference :MSDN

Read Full Post »

SQL Server – Recover the dropped Objects (View, Stored Procedure, Function & Trigger)

Posted in Solutions, SQL SERVER, tagged , , , , , on December 4, 2012| 16 Comments »

In my first article, I wrote about “How to recover the deleted data from SQL Server” from SQL server log. After this article, I received a lot of queries to recover dropped objects like tables, stored procedure, functions & triggers with the help of SQL server Log.

In this article, we will learn how to recover views, stored procedures, functions & triggers via SQL server log.

Step 1 :
Lets create few objects to explain the recovery process.

CREATE TABLE [dbo].[Student](
[Sno] [int] NOT NULL,
[Student ID] nvarchar(6) Not NULL ,
[Student name] [varchar](50) NOT NULL,
[Date of Birth]  datetime not null,
[Weight] [int] NULL)

GO
Create View Vw_Student
as
Select * from [Student]
GO
Create Procedure SP_Student
@StudentID nvarchar(6)
as
Select * from Student Where [Student ID] =@StudentID
GO
Create FUNCTION [dbo].[Fn_Student](@StudentID nvarchar(6))
RETURNS int
AS
Begin
Declare @Weight int
Select  @Weight = [Weight]
from Student Where [Student ID] =@StudentID
Return 	@Weight
End
GO
CREATE TRIGGER trg_Student
ON Student
FOR INSERT
AS RAISERROR (50009, 16, 10)
GO

Step 2:
Lets drop these objects.

Drop View [dbo].[Vw_Student]
GO
Drop Procedure [dbo].SP_Student
GO
Drop Function [dbo].[Fn_Student]
GO
Drop Trigger [dbo].[trg_Student]
GO

Step 3:
Check the existence of these objects to make sure that objects are dropped properly.

Select * from [Vw_Student]
GO
EXEC SP_Student 1
GO
Select dbo.[Fn_Student](1)

Step 4:
Create the given below stored procedure to recover the dropped objects.


-- Script Name: Recover_Dropped_Objects_Proc
-- Script Type : Recovery Procedure
-- Develop By: Muhammad Imran
-- Date Created: 04 Dec 2012
-- Modify Date:
-- Version    : 1.0

Create PROCEDURE Recover_Dropped_Objects_Proc
@Database_Name NVARCHAR(MAX),
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

DECLARE @Compatibility_Level INT
SELECT @Compatibility_Level=dtb.compatibility_level
FROM
master.sys.databases AS dtb WHERE dtb.name=@Database_Name

IF ISNULL(@Compatibility_Level,0)<=80
BEGIN
RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
RETURN
END

Select Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
from fn_dblog(NULL,NULL)
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
And [AllocUnitName]='sys.sysobjvalues.clst'
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DROPOBJ'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0
GO

--Execute the procedure like
--EXEC Recover_Dropped_Data_Proc 'Database Name'

----EXAMPLE #1 : FOR ALL Dropped Objects
EXEC Recover_Dropped_Objects_Proc 'test'
--GO
------EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Dropped_Objects_Proc 'test','2011/12/01','2013/01/30'
--RESULT

Read Full Post »

SQL SERVER – Discard result after query execution

Posted in Solutions, SQL SERVER, tagged , , , on December 3, 2012| Leave a Comment »

Today, I was optimizing few queries but due to the large number of records it was taking a while to display the result set and the execution plan and I was interested to view the execution plan instead of the result set to optimize it. But if you need the execution plan you need to wait for the result set to finish and then execution plan will be displayed.

So, I was searching for some options that could give me execution plan instead of result set only.

Finally, I found it. It is available in SSMS \ Tools \ Options \ Query Results \ Result to Grid \ Discard result after execution

By default, this option will be unchecked but to discard result set, you need to check it.

Lets go through step by step :

Step 1 :
Open the SSMS then go to Tools \ Options \ Query Results \ Result to Grid \ Discard Result after execution. You need to check it and press OK.

Step 2 :
Once you press OK, it will alert you, with “SQL Server Results to Grid option changes will only be applied to new SQL Server Query Editor windows”

Step 3 :
Open a new query window and execute the query. Now this time you will get the execution plan only.

Remember to include the execution plan otherwise it will display no execution plan.

Result set

Execution Plan

Read Full Post »