SQL SERVER – Reverse String Word By Word – Part 3

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. In this blog post we will be seeing the solution proposed by Yuri Petrov in this blog post.

Yuri’s script is in two parts.

Part 1: Create a auxiliary table with numbers.

CREATE TABLE Nums (num INT PRIMARY KEY);
WITH
cteNums8000
AS (
SELECT TOP (8000) ROW_NUMBER () OVER (ORDER BY (SELECT 0)) AS [num]
FROM sys.columns a
CROSS JOIN sys.columns b
)
INSERT INTO Nums
SELECT num FROM cteNums8000;
GO

Part 2: Script to Reverse String Words. If you want, you can convert this script to function afterwards.

DECLARE @ip VARCHAR (8000) = 'My Name is Pinal Dave';
WITH
cteSplit
AS (
SELECT SUBSTRING (@ip, num, CHARINDEX (' ', @ip + ' ', num) - num) AS [value], num
FROM Nums
WHERE num <= LEN (@ip)
AND
SUBSTRING (' ' + @ip, num, 1) = ' '
)
SELECT STUFF ((SELECT ' ' + value
FROM cteSplit
ORDER BY num DESC
FOR XML
path (''), TYPE).value ('.', 'varchar (8000)')
,
1, 1, '')

The above query will return results in the reverse order.

For example, as we have declared the variable as ‘My Name is Pinal Dave’, it will return results as ‘Dave Pinal is Name My’.

Thanks Yuri for awesome contribution.

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

SQL SERVER – Reverse String Word By Word – Part 2

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. I have taken in consideration both the comments and solution and build a more robust script over here.

Here is the script to create a function

CREATE FUNCTION [dbo].[Split] (@string AS VARCHAR(8000), @splitAt AS VARCHAR(8000))
RETURNS @strings TABLE (ID INT, Strings VARCHAR(8000))
AS
BEGIN
DECLARE
@splitLen AS INT
DECLARE
@index AS INT
DECLARE
@id AS INT
SET
@id = 0
SET @splitLen = LEN(@splitAt)
SET @splitAt = '%' + @splitAt + '%'
SET @index = PATINDEX(@splitAt, @string)
WHILE(@index > 0)
BEGIN
SET
@id = @id+1
INSERT INTO @strings VALUES(@id,SUBSTRING(@string, 1, @index-1))
SET @string = SUBSTRING(@string, @index + 1, LEN(@string))
SET @index = PATINDEX(@splitAt, @string)
END
IF
LEN(@string) > 0
BEGIN
SET
@id = @id+1
INSERT INTO @strings VALUES(@id, @string)
END
RETURN
END

Here is how you will use the function:

DECLARE @Input NVARCHAR(MAX)
SET @Input = 'This is Reverse String test'
DECLARE @Output NVARCHAR(MAX)
SET @Output = ''
SELECT @Output = strings + ' ' + @Output FROM dbo.split(@Input,' ')
ORDER BY ID
PRINT @Output

The above script will return results just as we have seen earlier blog post.

 

Thanks Sanjay for awesome contribution.

In addition to the above Yuri Petrov has also suggested another script which we will see in future blog posts.

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

SQL SERVER – Reverse String Word By Word

Earlier I wrote a blog post where I have reversed the entire string which was passed to the function. You can read that over here User Defined Functions (UDF) to Reverse String. In that blog post I wrote a function which would reverse an entire string. However, recently I read a question in SQLBangalore where the user wanted to reverse string, but wanted to keep all the words in the same order. For example,

If the string is – “I am Pinal Dave”, it should be reversed as “Dave Pinal am I.”

Well, here is the function which does the same task.

CREATE FUNCTION [dbo].[fn_ReverseWordsInSentence]
(
@ip VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE
@op VARCHAR(MAX)
SET  @op = ''
DECLARE @Lenght INT
WHILE
LEN(@ip) > 0
BEGIN
IF CHARINDEX
(' ', @ip) > 0
BEGIN
SET
@op = SUBSTRING(@ip,0,CHARINDEX(' ', @ip)) + ' ' + @op
SET @ip = LTRIM(RTRIM(SUBSTRING(@ip,CHARINDEX(' ', @ip) + 1,LEN(@ip))))
END
ELSE
BEGIN
SET
@op = @ip + ' ' + @op
SET @ip = ''
END
END
RETURN
@op
END
-- Usage
SELECT  [dbo].[fn_ReverseWordsInSentence] ('My Name is Pinal Dave')
/*
Dave Pinal is Name My
*/

Let me know if you have a better way to solve this problem.

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

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)

SQL SERVER – Order By Numeric Values Formatted as String

When I was writing this blog post I had a hard time to come up with the title of the blog post so I did my best to come up with one. Here is the reason why? I wrote a blog post earlier SQL SERVER – Find First Non-Numeric Character from String. One of the questions was that how that blog can be useful in real life scenario. This blog post is the answer to that question. Let us first see a problem.

We have a table which has a column containing alphanumeric data. The data always has first as an integer and later part as a string. The business need is to order the data based on the first part of the alphanumeric data which is an integer. Now the problem is that no matter how we use ORDER BY the result is not produced as expected. Let us understand this with example.

Prepare a sample data:

-- How to find first non numberic character
USE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))
GO
INSERT INTO MyTable (ID, Col1)
SELECT 1, '1one'
UNION ALL
SELECT 2, '11eleven'
UNION ALL
SELECT 3, '2two'
UNION ALL
SELECT 4, '22twentytwo'
UNION ALL
SELECT 5, '111oneeleven'
GO
-- Select Data
SELECT *
FROM MyTable
GO

The above query will give following result set.

Now let us use ORDER BY COL1 and observe the result along with Original SELECT.

-- Select Data
SELECT *
FROM MyTable
GO
-- Select Data
SELECT *
FROM MyTable
ORDER BY Col1
GO

The result of the table is not as per expected.

We need the result in following format.

Here is the good example of how we can use PATINDEX.

-- Use of PATINDEX
SELECT ID,
LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1) 'Numeric Character',
Col1 'Original Character'
FROM MyTable
ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1)
GO

We can use PATINDEX to identify the length of the digit part in the alphanumeric string (Remember: Our string has a first part as an int always. It will not work in any other scenario). Now you can use the LEFT function to extract the INT portion from the alphanumeric string and order the data according to it.

You can easily clean up the script by dropping following table.

DROP TABLE MyTable
GO

Here is the complete script so you can easily refer it.

-- How to find first non numberic character
USE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))
GO
INSERT INTO MyTable (ID, Col1)
SELECT 1, '1one'
UNION ALL
SELECT 2, '11eleven'
UNION ALL
SELECT 3, '2two'
UNION ALL
SELECT 4, '22twentytwo'
UNION ALL
SELECT 5, '111oneeleven'
GO
-- Select Data
SELECT *
FROM MyTable
GO
-- Select Data
SELECT *
FROM MyTable
ORDER BY Col1
GO
-- Use of PATINDEX
SELECT ID,
Col1 'Original Character'
FROM MyTable
ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1)
GO
DROP TABLE MyTable
GO

Well, isn’t it an interesting solution. Any suggestion for better solution? Additionally any suggestion for changing the title of this blog post?

Click to Download Scripts

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

SQL SERVER – Find First Non-Numeric Character from String

It is fun when you have to deal with simple problems and there are no out of the box solution. I am sure there are many cases when we needed the first non-numeric character from the string but there is no function available to identify that right away. Here is the quick script I wrote down using PATINDEX. The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character.

-- How to find first non numberic character
USE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))
GO
INSERT INTO MyTable (ID, Col1)
SELECT 1, '1one'
UNION ALL
SELECT 2, '11eleven'
UNION ALL
SELECT 3, '2two'
UNION ALL
SELECT 4, '22twentytwo'
UNION ALL
SELECT 5, '111oneeleven'
GO
-- Use of PATINDEX
SELECT PATINDEX('%[^0-9]%',Col1) 'Position of NonNumeric Character',
SUBSTRING(Col1,PATINDEX('%[^0-9]%',Col1),1) 'NonNumeric Character',
Col1 'Original Character'
FROM MyTable
GO
DROP TABLE MyTable
GO

Here is the resultset:

Where do I use in the real world – well there are lots of examples. In one of the future blog posts I will cover that as well. Meanwhile, do you have any better way to achieve the same. Do share it here. I will write a follow up blog post with due credit to you.

Click to Download Scripts

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

SQL SERVER – Concat Function in SQL Server – SQL Concatenation

Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had a great time delivering the session. During the session, we talked about SQL Server 2012 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though, SQL Server is now in moving very fast and have proved many times a better enterprise solution, it does not have some basic functions. I naturally asked him for an example and he suggested CONCAT() which exists in MySQL and Oracle.

The answer is very simple – the equivalent function in SQL Server to CONCAT() is ‘+’ (plus operator without quotes).

Method 1: Concatenating two strings

SELECT 'FirstName' + ' ' + 'LastName' AS FullName

Method 2: Concatenating two Numbers

SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))

Method 3: Concatenating values of table columns

SELECT FirstName + ' ' + LastName
FROM AdventureWorks.Person.Contact

Well, this may look very simple but sometimes it is very difficult to find the information for simple things only.

Do you have any such example which you would like to share with the community?

Watch a quick video relevent to this subject:

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