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)

SQL SERVER – Get Numeric Value From Alpha Numeric String – Get Numbers Only

I have earlier wrote article about SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only and it was very handy tool for me. Recently blog reader and SQL Expert Christofer has left excellent improvement to this logic. Here is his contribution. He has provided Stored Procedure and the same can be easily converted to Function.

CREATE PROCEDURE [dbo].[CleanDataFromAlpha]
@alpha VARCHAR(50),
@decimal DECIMAL(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON
;
DECLARE @ErrorMsg VARCHAR(50)
DECLARE @Pos INT
DECLARE
@CommaPos INT
DECLARE
@ZeroExists INT
DECLARE
@alphaReverse VARCHAR(50)
DECLARE @NumPos INT
DECLARE
@Len INT
-- 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
-- 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)
-- 3 Get the lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1))
+
','
+ SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)
-- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
-- if it's 0 after the handling, else we set @decimal to NULL
-- If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( '0' , @alpha ,1 )
-- Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX('%,%', @alpha)
IF (@CommaPos = '') BEGIN
SET
@CommaPos = 20
END
SET
@Pos = PATINDEX('%[^0-9]%',@alpha)
-- Replaces any aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe inside. The regular way
-- to solve this is to replace with ”, but then we miss the way to find the place to
-- put in the decimal.
WHILE (@Pos > 0) BEGIN
SET
@alpha = STUFF(@alpha, @pos, 1, '0')
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
END
IF
(@alpha IS NOT NULL AND @alpha != '') BEGIN
SET
@decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1))
+
'.'
+ SUBSTRING(@alpha, (@CommaPos + 1), 20))
END
-- Since we in this case don't want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET
@decimal = NULL
END
END
GO

If you run above SP as shown below it will work
DECLARE @myRetVal DECIMAL(14,5)
EXEC [CleanDataFromAlpha] 'ABC355,88ghf', @myRetVal OUTPUT
SELECT @myRetVal ReturnValue

ReturnValue
---------------------------------------
355.88000

(1 row(s) affected)

I once again want to thanks Christofer for his excellent contribution.

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

SQL SERVER – Function to Retrieve First Word of Sentence – String Operation

I have sent of function library where I store all the UDF I have ever written. Recently I received email from my friend requesting if I have UDF which manipulate string and returns only very first word of the statement. Well, I realize that I do not have such a script at all. I found myself writing down this similar script after long time. Let me know if you know any other better script to do the same task.

DECLARE @StringVar VARCHAR(100)
SET @StringVar = ' anything '
SELECT CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END FirstWordofSentence

Above script can be converted to User Defined Function which you can later use in SELECT statement as well. Here is how you can convert it to UDF and use it afterwords.

CREATE FUNCTION GetFirstWord (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END
RETURN
@rtStringVar
END
GO
--Usage of Function
SELECT dbo.GetFirstWord ('') AS FirstWordofSentence;
SELECT dbo.GetFirstWord (' Space Before') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('Long Statement') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('SingleWord') AS FirstWordofSentence;

You can see in the resultset where it will find the very first word of the any sentence.

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

SQL SERVER – Finding the Occurrence of Character in String

This article is written in response to provide hint to TSQL Beginners Challenge 14. The challenge is about counting the number of occurrences of characters in the string. Here is quick method how you can count occurrence of character in any string.

Here is quick example which provides you two different details.

  • How many times the character/word exists in string?
  • How many total characters exists in Occurrence?

Let us see following example and it will clearly explain it to you.

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)
SET @LongSentence = 'My Super Long String With Long Words'
SET @FindSubString = 'long'
SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,
(
LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

This will return following resultset.

CntReplacedChars     CntOccuranceChars
-------------------- --------------------
2                    2

If there is single word to find in the character, both the answer will be same. Here is the example of the same.

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)
SET @LongSentence = 'My Super Long String With Long Words'
SET @FindSubString = 'o'
SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,
(
LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

This will return following resultset.

CntReplacedChars     CntOccuranceChars
-------------------- --------------------
3                    3

I hope using this hint along with recursive CTE you will be able to solve the T-SQL Challenge. Additionally, make sure that your strings does not have leading or trailing empty spaces. If you have it, you may want to use LTRIM or RTRIM functions.

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

SQLAuthority News – Converting a Delimited String of Values into Columns

This blog post is about two great bloggers and their excellent series of blog posts. It was quite unusual to see two bloggers posting articles that are supporting each other and constantly improving the articles to the next level.

Two blogs which I am going to mention here are as follows: SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server() – Brad Schulz and Demystifying SQL Server – Adam Haines. Before continuing this blog post, I suggest you all to bookmark these blogs for future reference.

The whole thing started when Adam tried to answer the question “How to transform a delimited values into columns?” on MSDN SQL Forum. Adam made the first blog post here http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html and then Brad and Adam bounced a few ideas off the wall. Then, Adam started his series on concatenating column values http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html, and http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html.  In part two of the series, Brad wanted to get a deeper understanding of why the results came out the way they did. Adam then started another series on unpacking or parsing out delimited characters, http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html and here http://jahaines.blogspot.com/2009/12/splitting-delimited-string-part-2.html.  From here, Brad started digging even deeper into the internals of the XML method. Brad has a comprehensive list of the back and forth on this post, http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html and a final post here http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html.

The final outcome is that both the XML method and number table can perform equally, but the XML method has to be coded in a very particular way; otherwise performance will be horrendous. Also, the permanent number table shows consistently more performance than the inline number table; however, on smaller string sizes, the difference here is that most of the inline number tables require more CPU and RAM, whereas the permanent number table requires more IO. For an average developer, the number table solution is probably the easiest of the solutions to implement.

Overall, I strongly suggest to go through the abovementioned posts; you will love it and become their fan. Please note these posts are very easy to understand and if you know a bit of XML only, you will still be able to understand them very well. The above description has been taken with proper consent of the Adam and Brad.

Hats Off to you Guys! You guys inspire me and many SQL enthusiasts!

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