SQL SERVER – Reverse String Word By Word – Part 4

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara and Yuri Petrov contributed to the blog post with their excellent script which I have blogged earlier. In this blog post we will see a similar script by SQL Expert Paul G. He has essentially sliced the string up by the passed delimiter (in this case space) and reconstructed the string the reverse order.

Here is the script by Paul:

DECLARE @String NVARCHAR(50)
DECLARE @Delimiter CHAR(1)
DECLARE @count INT
DECLARE
@slice VARCHAR(8000)
DECLARE @rtnString NVARCHAR(50)
SET @Delimiter = ' '
SET @String = 'I am Pinal Dave'
SET @rtnString = ''
SET @count = 1
WHILE @count > 0
BEGIN
SET
@count = CHARINDEX(@Delimiter,@String)
IF (@count > 0)
SET @slice = LEFT(@String,@count - 1)
ELSE
SET
@slice = @String
IF(LEN(@slice) > 0)
SET @rtnString = @slice + ' ' + @rtnString
SET @String = RIGHT(@String,LEN(@String) - @count)
END
SELECT
@rtnString

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 Paul for awesome contribution.

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

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)