SQL SERVER – Check If String is a Palindrome in Using T-SQL Script – Reverse Function

One of my friends who works in a big MNC recently asked me that if there is any way to check if the String is Palindrome or not. The palindrome is a word, phrase, or sequence that reads the same backward as forward. For example A man, a plan, a canal – Panama! is palindrome so as Was it a car or a cat I saw?

My first reaction was to him was why does this kind of functionality. His answer was they have requirement in their business application where they are building captcha and they may display the image in mirror image as well as a part of challenge code and he can’t have any word which is palindrome as an option. For this he wanted to write a script which will go letter by letter and match them. If they are same, he will not use the word for captcha.

Honestly there is no need to write a function which compares it word by word. You can just use a REVERSE function to do this as well.

DECLARE @PalinString VARCHAR(256) = 'Was it a car or a cat I saw'
SELECT CASE WHEN REPLACE(@PalinString, ' ', '') = REVERSE(REPLACE(@PalinString, ' ', ''))
THEN 'Palindrome'
ELSE 'Not Palindrome' END AS [Answer]
GO

Again, if the word is not Palindrome you can just will get answered as it is not a palindrome. My script currently is removing all the spaces from the string. However, if your string is like A man, a plan, a canal – Panama! you may have to remove the exclamation mark and comma too using the REPLACE function.

Additionally, this works with foreign language as well. See the image below.

Let me know what you think of this function, do you think there should be a better trick to identify Palindrome? If yes, please leave a comment.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Significance of Table Input Parameter to Stored Procedure – Guest Post by Vikas Munjal – Koenig Solutions

SQL Server has introduced a functionality to pass a table data form into stored procedures and functions. This feature greatly simplifies the process of developing. The reason being,  we need not worry about forming and parsing XML data. With the help of the table Input parameter to Stored Procedure we can save many round trips. Any SQL training will vouch for the fact that SQL is capable of accepting large, complex data in the form of parameters in a stored procedure.

Situation

Consider two tables

1) Sales Table

2)  SalesDetails Table

dbo.Sales

dbo.SalesDetails

In the Sales Table various products are there for a specific Sales Id. The SalesDetails Table also displays costs of these products along with them.

SaleID=1, for instance, has PurchaseOrderNumber=’BigOrder’. It has three products- Product1,Product2 and Product3.

We should note that SalesId in Sales Table functions as the primary key and function of secondary key is performed by SalesId in SalesDetails Table.

Whenever there is a new sale, we want to impart a unique and distinctive number to the sale in the sales table. Also, we should provide the SalesDetails Table with the sold products in the product column.

Explanation

We have to generate two stored procedures to provide a solution to this situation. We use the first stored procedure for inserting the data in the dbo.Sales table. With the second stored procedure we store the data in  dbo.SalesDetails table.

In case, we consider the situation displayed in the table above, we should use the first stored procedure only once for adding the data in the dbo.Sales table. To add the data in the dbo.SalesDetails table, we have to use the second stored procedure thrice. A total of four round trips occurs between the SQL server and application. The number of round trips can be reduced to one, if we use table Input parameter to Stored Procedure.

Step 1 – Open a new query window to the tempdb database

USE tempdb;
GO

Step 2 – Create a Sales and SalesDetails table

CREATE TABLE dbo.Sales
( SaleID INT IDENTITY PRIMARY KEY,
CustomerID INT,
PurchaseOrderNumber VARCHAR(20)

);
CREATE TABLE dbo.SalesDetails
( SalesDetailID INT IDENTITY,
SaleID INT REFERENCES dbo.Sales(SaleID),
Description VARCHAR(50),
Price DECIMAL(18,2)
);
GO

Step 3 – Create traditional insert stored procedures for both tables

CREATE PROCEDURE dbo.SalesInsert
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SaleID INT OUTPUT
AS BEGIN
INSERT INTO
dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
END;
GO
CREATE PROCEDURE dbo.SalesDetailInsert
@SaleID INT,
@Description VARCHAR(50),
@Price DECIMAL(18,2),
@SalesDetailID INT OUTPUT
AS BEGIN
INSERT INTO
dbo.SalesDetails (SaleID,Description,Price)
VALUES(@SaleID,@Description,@Price);
SELECT @SalesDetailID = SCOPE_IDENTITY();
END;
GO

Step 4 – Show how we would have previously inserted an order

Here four round trips will occur in which we call dbo.SalesInsert stored procedure once to insert the data into dbo.Sales and dbo.SalesDetailInsert stored procedure thrice to insert all the products for a particular  sales id

DECLARE @SaleID INT;
DECLARE @SalesDetailID INT;
BEGIN TRAN;
EXEC dbo.SalesInsert 12,'BigOrder',@SaleID OUTPUT;
EXEC dbo.SalesDetailInsert @SaleID,'Product 1',12.3,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 2',14.6,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 3',122.35,@SalesDetailID OUTPUT
COMMIT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO

Now we create a table data type

Step 5 – Create a table data type to hold the sales details

CREATE TYPE dbo.SalesDetails AS TABLE
( Description VARCHAR(50),
Price DECIMAL(18,2)
);
GO

Step 6 – Modify the insert procedure to take detail lines as well

ALTER PROCEDURE dbo.SalesInsert
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SalesDetails dbo.SalesDetails READONLY,
@SaleID INT OUTPUT
AS BEGIN
BEGIN TRAN
;
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
SELECT @SaleID, Description,Price
FROM @SalesDetails;
COMMIT;
END;
GO

Step 7 – Perform an insert with a single round-trip

With the help of table data type to a stored procedure only one round trip is needed

DECLARE @SaleID INT;
DECLARE @SalesDetails dbo.SalesDetails;
INSERT INTO @SalesDetails VALUES('Product 1',12.3),('Product 2',14.66),('Product 3',122.35);
EXEC dbo.SalesInsert 12,'BigOrder',@SalesDetails,@SaleID OUTPUT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO

Table input parameter in SQL is a massive march ahead where development and potential performance are concerned. It can lessen server round trips, utilize table constraints and widen the functionality of programming on the database engine.

Guest Author

The author of the article Vikas Munjal is SQL Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, SharePoint, SQL course, Prince2, etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session.

“How do we know if today is a weekend or weekday using SQL Server Functions?”

Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012’s CHOOSE function. It is

SELECT GETDATE() Today,
DATENAME(dw, GETDATE()) DayofWeek,
CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
GO

You can use the choose function on table as well. Here is the quick example of the same.

USE AdventureWorks2012
GO
SELECT A.ModifiedDate,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
GO

If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function.

Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Removing Leading Zeros From Column in Table – Part 2

Earlier I wrote a blog post about Remvoing Leading Zeros from Column In Table. It was a great co-incident that my friend Madhivanan (no need of introduction for him) also post a similar article over on BeyondRelational.com. I strongly suggest to read his blog as well as he has suggested some cool solutions to the same problem.

On original blog post asked two questions 1) if my sample for testing is correct and 2) If there is any better method to achieve the same. The response was amazing. I am proud on our SQL Community that we all keep on improving on each other’s contribution. There are some really good suggestions as a comment. Let us go over them right now.

Improving the ResultSet

I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well.

USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '0001'
UNION ALL
SELECT '000100'
UNION ALL
SELECT '100100'
UNION ALL
SELECT '000 0001'
UNION ALL
SELECT '00.001'
UNION ALL
SELECT '01.001'
UNION ALL
SELECT '0000'
GO

Now let us go over some of the fantastic solutions which we have received.

Response from Rainmaker

SELECT
CASE PATINDEX('%[^0 ]%', Col1 + ' ‘')
WHEN 0 THEN ''
ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
END
FROM
Table1

Response from Harsh Solution 1

SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + 'a'), LEN(Col1))
FROM Table1

Response from Harsh Solution 2

SELECT
RIGHT(Col1, LEN(Col1)+1 -PATINDEX('%[^0 ]%', Col1 + 'a' ))
FROM Table1

Response from lucazav

SELECT
T.Col1
, label = CAST(
CAST(REPLACE(T.Col1, ' ', '') AS FLOAT)
AS VARCHAR(10))
FROM Table1 AS T

Response from iamAkashSingh

SELECT REPLACE(LTRIM(REPLACE(col1,'0',' ')),' ','0') FROM table1

Here is the resultset of above scripts. It will remove any leading zero or space and will display the number accordingly.

If you believe there is a better solution, please leave a comment. I am just glad to see so many various responses and all of them teach us something new.

Reference: Pinal Dave (http://blog.sqlauthority.com)

 

SQL SERVER – Using RAND() in User Defined Functions (UDF)

Here is the question I received in email.

“Pinal,

I am writing a function where we need to generate random password. While writing T-SQL I faced following issue. Everytime I tried to use RAND() function in my User Defined Function I am getting following error:

Msg 443, Level 16, State 1, Procedure RandFn, Line 7
Invalid use of a side-effecting operator ‘rand’ within a function.

Here is the simplified T-SQL code of the function which I am using:

CREATE FUNCTION RandFn()
RETURNS INT
AS
BEGIN
DECLARE
@rndValue INT
SET
@rndValue = RAND()
RETURN @rndValue
END
GO

I must use UDF so is there any workaround to use RAND function in UDF.”

Here is the workaround how RAND() can be used in UDF. The scope of the blog post is not to discuss the advantages or disadvantages of the function or random function here but just to show how RAND() function can be used in UDF.

RAND() function is directly not allowed to use in the UDF so we have to find alternate way to use the same function. This can be achieved by creating a VIEW which is using RAND() function and use the same VIEW in the UDF. Here is the step by step instructions.

Create a VIEW using RAND function.

CREATE VIEW rndView
AS
SELECT
RAND() rndResult
GO

Create a UDF using the same VIEW.

CREATE FUNCTION RandFn()
RETURNS DECIMAL(18,18)
AS
BEGIN
DECLARE
@rndValue DECIMAL(18,18)
SELECT @rndValue = rndResult
FROM rndView
RETURN @rndValue
END
GO

Now execute the UDF and it will just work fine and return random result.

SELECT dbo.RandFn()
GO

In T-SQL world, I have noticed that there are more than one solution to every problem. Is there any better solution to this question? Please post that question as a comment and I will include it with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Removing Leading Zeros From Column in Table

Some questions surprises me and make me write code which I have never explored before. Today was similar experience as well. I have always received the question regarding how to reserve leading zeroes in SQL Server while displaying them on the SSMS or another application. I have written articles on this subject over here.

Today I received a very different question where the user wanted to remove leading zero and white space. I am using the same sample sent by user in this example.

USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '0001'
UNION ALL
SELECT '000100'
UNION ALL
SELECT '100100'
UNION ALL
SELECT '000 0001'
UNION ALL
SELECT '00.001'
UNION ALL
SELECT '01.001'
GO
-- Original data
SELECT *
FROM Table1
GO
-- Remove leading zeros
SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1
GO
-- Clean up
DROP TABLE Table1
GO

Here is the resultset of above script. It will remove any leading zero or space and will display the number accordingly.

This problem is a very generic problem and I am confident there are alternate solutions to this problem as well. If you have an alternate solution or can suggest a sample data which does not satisfy the SUBSTRING solution proposed, I will be glad to include them in follow up blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Retrieving Random Rows from Table Using NEWID()

I have previously written about how to get random rows from SQL Server.

However, I have not blogged about following trick before. Let me share the trick here as well. You can generate random scripts using following methods as well.

USE AdventureWorks2012
GO
-- Method 1
SELECT TOP 100 *
FROM Sales.SalesOrderDetail
ORDER BY NEWID()
GO
-- Method 2
SELECT TOP 100 *
FROM Sales.SalesOrderDetail
ORDER BY CHECKSUM(NEWID())
GO

You will notice that using NEWID() in the ORDER BY will return random rows in the result set. How many of you knew this trick? You can run above script multiple times and it will give random rows every single time.

Watch a 60 second video on this subject

Note: This method can be very resource intensive for large resultsets.

Reference: Pinal Dave (http://blog.sqlauthority.com)