Parsing @@Version output question


I am using Powershell 2.0 with SQL snapin loaded and trying to get the SQL server Version, version number and Edition.

$ver = Invoke-Sqlcmd  -ServerInstance 'ServerName\InstanceName' -Query 'select @@version'

Column1 : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
            Apr  2 2010 15:48:46
            Copyright (c) Microsoft Corporation
            Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

My question is, how do I parse this single string output of four lines into

$SQLVersion = Microsoft SQL Server 2008 R2
$SQLVerNum = 10.50.1600.1
$SQLEdittion = Standard Edition (64-bit)
$Buil = Build 9200

I am assuming that i should be using RegEx, but not really getting anywhere (especially because I cant figure out how to look at a separate line










No Data
  • Instead of SELECT @@Version you can run this:

    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;
    The output from Invoke-SQLCmd with this command will be an object with ProductVersion,ProductLevel,Edition and EngineEdition as properties.
    If you wish to parse that exact output, the below is possible, however probably not useful for other versions...
    $result = Invoke-SQLCmd -Query "SELECT @@VERSION" | Select-Object -ExpandProperty column1
    $result = $result -split "`n"
    $SQLVersion = $($result[0] -split '-')[0].trim()
    $SQLVerNum = $($($result[0] -split '-')[1].trim() -split " ")[0]
    $SQLEdittion = $($result[3] -split "\son\s")[0].trim()
    $Build = $($($($result[3] -split "\son\s") -split "\(")[3] -split "\:")[0].trim()
No Data