Feeds:
Posts
Comments

Archive for the ‘Solutions’ Category

SQL SERVER – Extract part of a string

Posted in Solutions, SQL SERVER, tagged , , , on June 8, 2013| 2 Comments »

Few days ago, I was working on a report and I came across an issue wherein I had a varchar [ID] column with alphanumeric data in it but I had to display only number from this column (no strings). I provided this solution long back and you can use it here as well. But given below is the better solution to accommodate this issue.

Let me create an example to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[String] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RCP0001')
INSERT INTO tbl_sample VALUES (2,'TEMP0231')
INSERT INTO tbl_sample VALUES (3,'PO0999')
INSERT INTO tbl_sample VALUES (4,'SO0341')
INSERT INTO tbl_sample VALUES (5,'SUP12')
INSERT INTO tbl_sample VALUES (6,'CUST76')
INSERT INTO tbl_sample VALUES (7,'241')
INSERT INTO tbl_sample VALUES (8,'0000')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

extractnumbers1.1

Solution :
You need to use PATINDEX to get the first non numeric character location and then you can use SUBSTRING to get the all numeric values.
Given below is the script.

USE tempdb
GO
SELECT
[ID]
, [String]
, SUBSTRING([String],PatIndex('%[0-9]%',[String])
,DATALENGTH([String])) AS [Numbers]
FROM tbl_sample
GO
--OUTPUT

extractnumbers1.2

Read Full Post »

SQL SERVER – how to remove first two characters from year

Posted in Solutions, SQL SERVER, tagged , , , , on June 7, 2013| Leave a Comment »

Recently, I was developing a report in a legacy application. In that application we had used SQL Server 2005 and later on migrated to SQL Server 2012. The problem I came across while developing the report was I needed the ‘year’ in two digit format. Normally the year is in four digits like 2013 but it should be in two digits only like 13 to match with the saved data in the tables.

Given below are the two different methods.
Method 1 :
In this method, we will use TWO functions (RIGHT & YEAR) to achieve it.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()
SELECT RIGHT(YEAR(@datetime),2) AS [Two digit year]
--OUTPUT

Two digit year
————–
13

(1 row(s) affected)

Method 2 :
In the second method, we will use only ONE function (FORMAT) to achieve it.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()
SELECT FORMAT(@datetime,'yy') AS [Two digit year]
--OUTPUT

Two digit year
————–
13

(1 row(s) affected)

Read Full Post »

SQL SERVER – How to sum a varchar column

Posted in Solutions, SQL SERVER, tagged , , , on June 6, 2013| 4 Comments »

We usually see this kind of problem in a legacy system wherein the datatype of a table’s column is something but the data stored in it belongs to some different datatypes. And most of the time these datatypes are compatible with each other so it does not create any issues at the time of insertion. But, when it comes to data manipulation you feel handicapped. However, you can resolve these issues using some scripting. But you must remember that before doing any manipulation you must know the actual data is in which datatype (small integer, integer, bigint, numeric, decimal, money, float) and according to that, you need to convert and then manipulate it.

Lets resolve this issue step by step :

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Column_varchar] VARCHAR(10)
)
GO

Step 2 :
Insert some dummy data to perform aggregate SUM on column ([Column_varchar]). Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1,12345)
INSERT INTO tbl_sample VALUES (2,1.234)
INSERT INTO tbl_sample VALUES (3,'a64')
INSERT INTO tbl_sample VALUES (4,'1,200')

Step 3 :
Browse the data from the table and check the datatypes. Given below is the script.

USE tempdb
GO
SELECT * FROM tbl_sample

sumvarcharcolumn1.1

Step 4 :
As you can see there is a ‘,’ (Comma) in ID no 4 in the table. If you find cases like this, you need to use money datatype to convert.
Given below is the script to convert it.

USE tempdb
GO
SELECT
(CASE WHEN ISNUMERIC([Column_varchar])=1
THEN CONVERT(MONEY
,[Column_varchar]) ELSE 0 END)
AS [Converted to Numeric]
FROM tbl_sample
GO

sumvarcharcolumn1.3

Step 5 :
Once you convert it into any number datatype after that just perform any aggregate function on it. Lets SUM the column ([column varchar]) in the table (tbl_sample).
Given below is the script.

SELECT
SUM((CASE WHEN ISNUMERIC([Column_varchar])=1
THEN CONVERT(MONEY,[Column_varchar]) ELSE 0 END)
)
AS [Converted to Numeric]
FROM tbl_sample
GO

sumvarcharcolumn1.2

Read Full Post »

SQL SERVER – How to store more than 8000 characters in a column

Posted in Solutions, SQL SERVER, tagged , , , on June 4, 2013| 1 Comment »

I received an inquiry from one of my blog readers “Mr. Arun” and he wanted to store more than 8,000 characters in a column. Generally the length of  a varchar(Max) data type consider it as a 8000 characters and above. So I suggested him to use VARCHAR(MAX). But even if you use VARCHAR(MAX), you should be careful while working on more than 8000 characters.
Let me explain the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Column_varchar] VARCHAR(MAX)
)
GO

Step 2 :
Insert 10,000 characters in the column ([Column_varchar]). Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1, REPLICATE('A',10000))

Step 3 :
Check the length of column ([Column_varchar]) to see if 10,000 characters are inserted or not. Given below is the script.

USE tempdb
GO
SELECT DATALENGTH([Column_varchar]) AS [Column Length]
FROM tbl_sample
--OUTPUT

morethan80001.1

Step 4 :
Ooopps…… It only inserted 8000 characters even though I passed 10,000. Basically the solution is that you need to cast the characters as VARCHAR(MAX) before insertion and insert it again.

USE tempdb
GO
INSERT INTO tbl_sample VALUES
(2, REPLICATE(CAST('A' AS VARCHAR(MAX)),10000))

Step 5 :
Check the length of column ([Column_varchar]) AGAIN and see whether 10,000 characters are inserted or not. Given below is the script.

USE tempdb
GO
SELECT DATALENGTH([Column_varchar]) AS [Column Length]
FROM tbl_sample
--OUTPUT

morethan80001.2

As you can see, this time it has inserted more than 8000 characters.

Conclusion :
Remember, whenever you are planning to insert more than 8000 characters to any varchar column, you must cast it as varchar(max) before insertion.

Read Full Post »

SQL SERVER – Convert short month name to month name

Posted in Solutions, SQL SERVER, tagged , , , , on May 31, 2013| 2 Comments »

Few days ago, I was working on a report and came across with an issue regarding conversion of Short month name to Month name and we do not have any built-in function to do it in SQL Server. So I commenced my research and finally found few solutions.

Before proceeding with the solutions, let me create a sample to demonstrate this solution.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
[ID] INT,
[SHORT_MONTH_NAME] VARCHAR(3)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'Jan' UNION ALL
SELECT 2, N'Feb' UNION ALL
SELECT 3, N'Mar' UNION ALL
SELECT 4, N'Apr' UNION ALL
SELECT 5, N'May' UNION ALL
SELECT 6, N'Jun' UNION ALL
SELECT 7, N'Jul' UNION ALL
SELECT 8, N'Aug' UNION ALL
SELECT 9, N'Sep' UNION ALL
SELECT 10, N'Oct' UNION ALL
SELECT 11, N'Nov' UNION ALL
SELECT 12, N'Dec'
GO

SELECT * FROM tbl_MonthName
GO
--OUTPUT

MonthShortName1.1

Method 1 :
In this method, we will use the old approach. In this approach, we need to manually concatenate day with short month name and year to make a complete date of that particular month using additional single quotes and then get the Month name from this concatenate date using DATENAME function. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[SHORT_MONTH_NAME] AS [SHORT MONTH NAME]
,DATENAME(MONTH,CONVERT(
DATETIME,'1'+ [SHORT_MONTH_NAME] + '1')) AS [MONTH NAME]
FROM tbl_MonthName
GO
--OUTPUT

MonthShortName1.2

Method 2 :
In this method, we will use the New approach. In this approach, we need to automatically concatenate day with short month name and year to make a complete date of that particular month using CONCAT function (without any single quotes) and then get the Month Name using FORMAT function. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
,[SHORT_MONTH_NAME] AS [SHORT MONTH NAME]
,FORMAT(CONVERT(
DATETIME,CONCAT(1,[SHORT_MONTH_NAME],1)),'MMMM') AS [MONTH NAME]
FROM tbl_MonthName
GO
--OUTPUT

MonthShortName1.2

Conclusion :
In both methods, we used different approaches using manual concatenation and automatic concatenation using CONCAT function. I would recommend Method 2 because in my earlier article I found that  CONCAT function is much faster than the normal concatenations.

Read Full Post »

SQL SERVER – Passing multiple values through one parameter in a stored procedure

Posted in Solutions, SQL SERVER, tagged , , , , on May 31, 2013| 16 Comments »

In my earlier article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure.

Given below are the two different Methods :

Method 1 : Using XQuery

In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to convert the string parameter to xml variable and split it using comma delimiter and then you can query it.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_Employeelist
@Cities NVARCHAR(30)
AS
DECLARE @CitiesXML AS XML
SET @CitiesXML = cast(('<a>'+replace(@Cities,',' ,'</a><a>')
+'</a>') AS XML)

SELECT
BusinessEntityID
, FirstName
, JobTitle
, City
FROM HumanResources.vEmployee
WHERE City IN
(
SELECT
A.value('.', 'varchar(max)')
FROM @CitiesXML.nodes('A') AS FN(A)
)
ORDER BY BusinessEntityID
GO

--Execute the stored procedure using multiple values
--through one parameter in this stored procedure
USE AdventureWorks2012
GO
EXEC usp_Employeelist
'Cambridge,Newport Hills,Berlin,Bordeaux'
GO
--OUTPUT

Passingmultiple1.1

Method 2 : Using Dynamic queryhe 

In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to replace the parameter with single quote and create a dynamic query and execute it.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_Employeelist
@Cities NVARCHAR(30)
AS

DECLARE @Sql VARCHAR(MAX)
SET @Cities = REPLACE(@Cities,',',''',''')

SET @Sql = 'SELECT
BusinessEntityID
, FirstName
, JobTitle
, City
FROM HumanResources.vEmployee
WHERE City IN
(
''' + @Cities + '''
)
ORDER BY BusinessEntityID'

EXEC (@Sql)
GO

--Execute the stored procedure using multiple values
--through one parameter in this stored procedure
USE AdventureWorks2012
GO
EXEC usp_Employeelist
'Cambridge,Newport Hills,Berlin,Bordeaux'
GO
--OUTPUT

Passingmultiple1.1

Read Full Post »

SQL SERVER – Multiple ways to remove Milliseconds from Datetime (Truncate Datetime till Second)

Posted in Solutions, SQL SERVER, tagged , , , , on May 30, 2013| 5 Comments »

I came across this problem recently, when I was working on a report and I needed to truncate the date time till seconds. Given below are the two different methods we can use to achieve it.

Method 1 :
In this method, we will first convert the datetime value to varchar, truncate it and then convert it back to datetime data type using CONVERT function twice.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CONVERT(DATETIME,CONVERT(VARCHAR(20),@datetime,120))
AS [Datetime without millisconds]
GO
--OUTPUT

truncatemilliseconds

Method 2 :
In this method, we will first FORMAT the datetime value to a defined format (truncate milliseconds) and then convert it back to datetime data type using CAST function.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CAST(FORMAT(@datetime,'yyyy-MM-dd HH:mm:ss') AS datetime)
AS [Datetime without millisconds]
GO
--OUTPUT

truncatemilliseconds1.1

Read Full Post »

SQL SERVER – Faster way to calculate running total

Posted in Solutions, SQL SERVER, tagged , , , on May 25, 2013| 2 Comments »

A couple of days ago, I developed a tool how to recover the dropped index without backup. In this tool, running total needs to be generated. So I did some research and came across couple of options along with the best option as well.

Given below are the three methods :

Method 1:
In this method, we will use the self join to calculate running total.

--This script will work on SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
A.SalesOrderID AS [SalesOrderID]
, A.SalesOrderDetailID AS [SalesOrderDetailID]
, A.ProductID AS [ProductID]
, A.LineTotal AS [LineTotal]
, SUM(B.lineTotal) AS [Running Total]

FROM SALES.SalesOrderDetail A
INNER JOIN Sales.SalesOrderDetail B
ON A.SalesOrderID =B.SalesOrderID
AND B.SalesOrderDetailID<=A.SalesOrderDetailID
WHERE A.SalesOrderID =43659
GROUP BY
A.SalesOrderID
, A.SalesOrderDetailID
, A.ProductID
, A.LineTotal
ORDER BY
A.SalesOrderID
, A.SalesOrderDetailID
, A.ProductID
, A.LineTotal
GO

Method 2:
In this method, we will use the sub query to calculate running total.

--This script will work on SQL Server 2005 and above
USE AdventureWorks2012
GO
SELECT
A.SalesOrderID AS [SalesOrderID]
, A.SalesOrderDetailID AS [SalesOrderDetailID]
, A.ProductID AS [ProductID]
, A.LineTotal AS [LineTotal]
, (
SELECT SUM(B.lineTotal)
FROM SALES.SalesOrderDetail B
WHERE A.SalesOrderID =B.SalesOrderID
AND B.SalesOrderDetailID<=A.SalesOrderDetailID
) AS [Running Total]
FROM SALES.SalesOrderDetail A
WHERE A.SalesOrderID =43659
GO

Method 3:
In this method, we will use the SUM function to calculate running total.

--This script will work on SQL Server 2008 R2 and above.
USE AdventureWorks2012
GO
SELECT
SalesOrderID
, SalesOrderDetailID
, ProductID
, LineTotal
, SUM(LineTotal)
OVER(PARTITION BY SalesOrderID
ORDER BY SalesOrderID,SalesOrderDetailID)
AS [Running Total]
FROM SALES.SalesOrderDetail
WHERE SalesOrderID =43659
GO

All of the above queries will give you the same result set. However lets view their performance .
runningtotal.1.1

Conclusion :
As you can see, the performance of Method 3 is better than other methods. Also, if you look at the line of code, it is also shorter in Method 3 vis-à-vis other methods. So, I would recommend method 3.

Read Full Post »

SQL SERVER – Resetting sequence values for entire database

Posted in Solutions, SQL SERVER, tagged , , , , , , , on May 24, 2013| Leave a Comment »

It is a common practice that whenever you need to deploy a production database, you need to first delete all the test data and reset almost all the identity values in the entire database. In SQL Server 2012 also, you need to do the same but in addition to this you need to reset the SEQUENCE object as well (if utilized).
Lets do it step by step.

Step 1 :
First of all, you need to check whether the SEQUENCE object exits in your database or not and what its current value is.
Given below is the script.

USE AdventureWorks2012
GO
SELECT
name AS [Sequence Name]
,SCHEMA_NAME(schema_id) AS [Scheme Name]
,[start_value] AS [Start Value]
,[current_value] AS [Current Value]
FROM sys.sequences
GO
--OUTPUT

resetsequence1.2

Step 2 :
As you can see, in the above list of sequences, the current value of sequences is not equal to the start value. Lets create the script to reset the sequences.

USE AdventureWorks2012
GO
SELECT
'ALTER SEQUENCE '
+  QUOTENAME(schema_name(schema_id))
+  '.'
+  QUOTENAME(name)
+  ' RESTART WITH '
+  TRY_CONVERT(nvarchar(50),[start_value])
AS [QUERY]
FROM sys.sequences
GO
--OUTPUT

resetsequence1.1

Step 3 :
Once you executed the above query, just copy the result set of the above query and paste it on to a new query window. Make sure you are in a correct database and then execute the result set of above query.

USE AdventureWorks2012
GO
ALTER SEQUENCE [dbo].[Ticket] RESTART WITH 1
ALTER SEQUENCE [dbo].[PurchaseOrderID] RESTART WITH 1
ALTER SEQUENCE [dbo].[SalesOrderID] RESTART WITH 1
ALTER SEQUENCE [dbo].[EmployeeID] RESTART WITH 1
ALTER SEQUENCE [dbo].[ContactID] RESTART WITH 1
GO
--OUTPUT

resetsequence1.3

Step 4 :
Once you execute the above reset statements in a new query window, execute the STEP 1 query again to check whether sequences are reset or not.

USE AdventureWorks2012
GO
SELECT
name AS [Sequence Name]
,SCHEMA_NAME(schema_id) AS [Scheme Name]
,[start_value] AS [Start Value]
,[current_value] AS [Current Value]
FROM sys.sequences
GO
--OUTPUT

resetsequence1.4

Now, you can see that all the sequence objects have been reset.

Read Full Post »

SQL SERVER – How to get short month name / weekday name from datetime

Posted in Solutions, SQL SERVER, tagged , , , , , on May 23, 2013| Leave a Comment »

I came across this question on multiple forums and it is being solved using multiple function such as DATENAME and LEFT function functions. However, lets solve it using FORMAT function only.
Let me create an example to explain it.

USE tempdb
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-02-02')
INSERT INTO tbl_Sample VALUES (3,'2013-03-03')
INSERT INTO tbl_Sample VALUES (4,'2013-04-04')
INSERT INTO tbl_Sample VALUES (5,'2013-05-05')
INSERT INTO tbl_Sample VALUES (6,'2013-06-06')
INSERT INTO tbl_Sample VALUES (7,'2013-07-07')

Method 1 :
In this method, we will use DATENAME & LEFT function to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
, [Date]
, LEFT(DATENAME(MONTH,[Date]),3) AS [Short Month Name]
, LEFT(DATENAME(WEEKDAY,[Date]),3) AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.1

Method 2 :
In this method, we will use ONLY FORMAT function (shipped in SQL Server 2012) to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
, [Date]
, FORMAT([Date],'MMM') AS [Short Month Name]
, FORMAT([Date],'ddd') AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.2

Read Full Post »

« Newer Posts - Older Posts »