SUBSTRING

Question

Post reply

SUBSTRING

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 5, 2010 at 1:04 pm

#227508

Hello Everyone
I know the Subject Title is pretty sad.
This is what I am trying to get from the string returned from @@VERSION
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
I would like to have Only the number "2008" and nothing else.
Any suggestion as to what I can do to get that with the String manipulation?
Thanks in advance
Andrew SQLDBA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Andrew , are u trying to find if your SQL Server Version is 2005 or 2008 ? or rather which version of SQL Server?

Adiga One Orange Chip Points: 27284 More actions · Answer 2

You may use something like this

select right(left('Microsoft SQL Server 2008',28),4)

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

SELECT SUBSTRING(@@VERSION, PATINDEX('%[0-9][0-9][0-9][0-9]%', @@VERSION), 4)

Scott Pletcher, SQL Server MVP 2008-2010

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Scott

Thank you very much, this is exactly what I was looking for.

Andrew SQLDBA

Dugi SSCoach Points: 17998 More actions · Answer 5

scott.pletcher (8/5/2010)
SELECT SUBSTRING(@@VERSION, PATINDEX('%[0-9][0-9][0-9][0-9]%', @@VERSION), 4)

That's great!!! - really!

Can you explain little bit, how you find the idea, to use PATINDEX like this in your very nice & simple script !?! :w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 6

Yes, I think it is really Great. I would have never thought to use it, that is why I asked on here.

Very nice script

Thank You again

Andrew SQLDBA

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 7

Lol. Not sure, just an idea that pops into your head.

When you look at the output of version, you see that the first four consecutive digits in the result are always the SQL year, so that pattern just picks up the first four consecutive digits 🙂 .

Scott Pletcher, SQL Server MVP 2008-2010

Dugi SSCoach Points: 17998 More actions · Answer 8

AndrewSQLDBA (8/5/2010)
Yes, I think it is really Great. I would have never thought to use it, that is why I asked on here.
Very nice script
Thank You again
Andrew SQLDBA

This is the an MVP's way !!! - really cool!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 9

scott.pletcher (8/5/2010)
Lol. Not sure, just an idea that pops into your head.
When you look at the output of version, you see that the first four consecutive digits in the result are always the SQL year, so that pattern just picks up the first four consecutive digits 🙂 .

The very interesting part is : "Not sure, just an idea that pops into your head"

Once again - it's cool, yea pops into your head!!! :w00t::w00t::w00t:

Thank you! 😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]