0

I am doing automation of security bulleting of SQL Server and I need to extract security bulletin version from @@version

select @@version

Output

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
Jun 17 2016 19:14:09 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I need to extract information (KB3171021) from above output.

This is something I tried

DECLARE @version NVARCHAR(400)

SELECT @version = REVERSE(SUBSTRING(@@version, 0, 50))

SELECT @version

SELECT SUBSTRING(@version, (CHARINDEX('(', REVERSE(@@VERSION))),(CHARINDEX(')', REVERSE(@@VERSION))))

I am not getting desired output. Can you please help?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
deepti
  • 729
  • 4
  • 17
  • 38

1 Answers1

1

Well, As I've shown in the link to rextester in my comment, the @@version might not contain the data you are looking for.

You might also want to check out SERVERPROPERTY

However, if your systems will always contain it, the simplest way to extract it would be to get the data inside the second parenthesis:

;WITH CTE AS
(
    SELECT  CHARINDEX('(', @@Version, CHARINDEX('(', @@Version) + 1) As SecondOpen,
            CHARINDEX(')', @@Version, CHARINDEX(')', @@Version) + 1) As SecondClose
)

SELECT  SUBSTRING(@@Version, SecondOpen + 1, SecondClose - SecondOpen - 1)
FROM CTE

Result:

KB3171021

You can see a live demo on rextester.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • yes but when iam using this SELECT @@Version As Original, SUBSTRING(@@Version, CHARINDEX('(', @@Version, (CHARINDEX('(', @@Version) + 1)) +1,(CHARINDEX('(', @@Version, CHARINDEX('(', @@Version) + 1) - CHARINDEX(')', @@Version, CHARINDEX(')', @@Version)) + 1) -1) As KB, @@Version As ActualVersion its not giving me result , i just combined everything in 1 query – deepti Sep 04 '17 at 08:31