Handy SQL Server Functions Series (HSSFS) Part 2.0 - Prelude to Parsing Patterns Properly
- by Most Valuable Yak (Rob Volk)
In Part 1 of the series I wrote about 2 lesser-known and somewhat undocumented functions. In this part, I'm going to cover some familiar string functions like Substring(), Parsename(), Patindex(), and Charindex() and delve into their strengths and weaknesses.
I'm also splitting this part up into sub-parts to help focus on a particular technique and/or problem with the technique, hence the Part 2.0. Consider this a composite post, or com-post, if you will. (It may just turn out to be a pile of sh_t after all)
I'll be using a contrived example, perhaps the most frustratingly useful, or usefully frustrating, function in SQL Server: @@VERSION. Contrived, because there are better ways to get the information (which I'll cover later); frustrating, because of the way Microsoft formatted the value; and useful because it does have 1 or 2 bits of information not found elsewhere.
First let's take a look at the output of @@VERSION:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
There are 4 lines, with lines 2-4 indented with a tab character. In case your browser (or this blog software) doesn't show it correctly, I gave each line a different color. While this PRINTs nicely, if you SELECT @@VERSION in grid mode it all runs together because it ignores carriage return/line feed (CR/LF) characters. Not fatal, but annoying.
Note that @@VERSION's output will vary depending on edition and version of SQL Server, and also the OS it's installed on. Despite the differences, the output is laid out the same way and the relevant pieces are in the same order.
I'll be using the following view for Parts 2.1 onward, so we have a nice collection of @@VERSION information:
create view version(SQLVersion,VersionString) AS (
select 2000, 'Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)'
union all
select 2005, 'Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
May 26 2009 14:24:20
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)'
union all
select 2008, 'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)'
union all
select 2005, 'Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86)
Sep 6 2009 01:43:32
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)'
union all
select 2008, 'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)'
union all
select 2008, 'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)'
)
Feel free to add your own @@VERSION info if it's not already there.
In Part 2.1 I'll focus on extracting the SQL Server version number (10.50.1600.1 in first example) and the Edition (Developer), but will have a solution that works with all versions. Stay tuned!
