Feeds:
Posts
Comments

Archive for November, 2013

How to remove extra spaces from string value is a common issue and we usually come across this issue while massaging the data. I used this solution as a part of a solution in one of my earlier solutions.

Let me create a sample to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create table
CREATE TABLE tbl_sample
(
 [ID] INT,
 [NAME] VARCHAR(100)
)
GO
--Insert few records in the table
--Note : Names have been taken from adventureworks2012 database.
INSERT INTO tbl_sample VALUES (1,'Terri    Lee          Duffy')
INSERT INTO tbl_sample VALUES (2,'Roberto     Tamburello')
INSERT INTO tbl_sample VALUES (3,'Rob   Walters')
INSERT INTO tbl_sample VALUES (4,'Gail   A        Erickson')
INSERT INTO tbl_sample VALUES (5,'Gigi      N      Matthew')
GO
--Browse table
SELECT
 [ID]
,[NAME] AS [String with extra spaces]
FROM tbl_sample
--OUTPUT

remove extra spaces.1.2

SOLUTION 1 : Using REPLACE(string) Function
In this solution, we need to use the built-in function REPLACE to remove extra spaces from string value.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
 [ID]
,[NAME] AS [String with extra spaces]
,REPLACE(REPLACE(REPLACE([NAME]
,CHAR(32),'()'),')(',''),'()',CHAR(32))
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

SOLUTION 2 : Using User Defined Function
In this solution, we need to create a User Defined Function to remove extra spaces from string using XML.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
(
   @String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Extra_Space 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 @Removed_Extra_Space=REPLACE(
 Stuff((
 SELECT ';' + A.[Column]
 FROM CTE A
 WHERE ISNULL(A.[Column],'') <>''
 FOR XML PATH('')),1,1,''),';',' ')

RETURN (@Removed_Extra_Space)
END
GO

SELECT
 [ID]
,[NAME] AS [String with extra spaces]
,dbo.[UDF_Remove_Extra_Space_From_String] ([Name])
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

Read Full Post »

Today, I was developing a customer analysis report, basically trying to find out customer’s trend of purchasing. To get the trend I need to get his next row (purchasing) result set in the current row for comparison purposes.  Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution, using LEAD (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [ID] int,
 [Levels] varchar(50)
)
GO

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

get the previous row result.1.1

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
 A.ID
,A.Levels As [Current Level]
,B.Levels AS [Next Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID+1=B.ID
ORDER BY A.ID
GO
--OUTPUT

get value from next row

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LEAD function and it will calculate the next result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
 A.ID
,A.Levels As [Current Level]
,LEAD(A.levels,1,0) OVER (ORDER BY A.ID) AS [Next Level]
FROM tbl_sample A
GO
--OUTPUT

get value from next row

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.

Read Full Post »

A few days ago, I was working on list of customers and all the customers’ names were in upper case. Coincidentally, I found one customer having upper and lower case in his name. So, I thought of checking the entire customer list if anyone was having name in upper and lower case.
Note : The database is not case sensitive.

Let me create a sample to demonstrate the solution.

USE tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Name] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RARESQL.COM')
INSERT INTO tbl_sample VALUES (2,'RaReSql.com')
INSERT INTO tbl_sample VALUES (3,'Raresql')
INSERT INTO tbl_sample VALUES (4,'raresql.com')
GO
SELECT * FROM tbl_sample
--OUTPUT

lowercase.1.1

SOLUTION 1 : Using UPPER (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample WHERE
UPPER([NAME]) COLLATE Latin1_General_CS_AS !=[NAME]
--OUTPUT

lowercase.1.2

SOLUTION 2 : Using PATINDEX (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample
WHERE PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
,[NAME] COLLATE Latin1_General_CS_AS)>0
GO
--OUTPUT

lowercase.1.2

SOLUTION 3 : Using ASCII(String) Function
In this solution, it will not only give you the rows having lower case letter but it will also give you what lower characters are there in those rows. Given below is the script.

USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Extract_small_letters_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Extract_small_letters_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 ASCII(SUBSTRING(@String,Nums.n,1)) BETWEEN 97 AND 122

RETURN @RETURN_STRING
END
GO

SELECT *,dbo.[UDF_Extract_small_letters_From_String]([NAME])
As [Lower cases letters]
FROM tbl_sample
--OUTPUT

lowercase.1.3

Read Full Post »

« Newer Posts