Using substring functions on version numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using substring functions on version numbers

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-25 : 14:56:20

I have a set of version numbers I'm trying to simplify and lop the build number off of. They look like this:

5.02.0018
1.00
1.0.2427.22463
4.0.0.1410

I want to turn it into

5.02
1.00
1.0
4.0

leaving aside 4.0 for a moment, how would I do this? I have an example that works for all of them but 1.00, which I have to leave out to prevent a negative string length:

     SELECT distinct left( Version, CHARINDEX('.', Version, 4) - 1 )
  FROM x.[UsageCounters] WHERE CHARINDEX('.', Version, 4) <> ''

I looked for a period after three characters, and then cut out the period. But I have a few cases where there is no second period, so I have to exclude them with the WHERE statement.

Ignorance is bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 15:12:13

[code]
SELECT CASE WHEN SecondPeriod = 0 THEN Column1 ELSE SUBSTRING(Column1, 1, CHARINDEX('.', Column1, CHARINDEX('.', Column1) + 1) - 1) END
FROM
(
SELECT Column1, CHARINDEX('.', Column1, CHARINDEX('.', Column1) + 1) AS SecondPeriod
FROM @t
) t

[/code]

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 15:12:15

[code] Case When charindex('.',version, 3) > 0 then substring(version,1, charindex('.',version, 3)-1) Else version end [/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-25 : 15:20:09

And just for fun :)

DECLARE @Table TABLE(Version VARCHAR(50))

INSERT @Table
SELECT '5.02.0018'
UNION ALL SELECT '1.00'
UNION ALL SELECT '1.0.2427.22463'
UNION ALL SELECT '4.0.0.1410'
UNION ALL SELECT '1'

SELECT 
	REVERSE(PARSENAME(REVERSE(Version), 1)) + 
	CASE 
		WHEN REVERSE(PARSENAME(REVERSE(Version), 2)) IS NULL THEN ''
		ELSE '.' + REVERSE(PARSENAME(REVERSE(Version), 2))
	END AS Version
FROM @Table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 15:50:38

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

-- Prepare sample data
DECLARE @Table TABLE(Version VARCHAR(50))

INSERT	@Table
SELECT	'5.02.0018' UNION ALL
SELECT	'1.00' UNION ALL
SELECT	'1.0.2427.22463' UNION ALL
SELECT	'4.0.0.1410' UNION ALL
SELECT	'1'

-- Peso
SELECT	Version,
	dbo.fnParseString(-1, '.', Version) + ISNULL('.' + dbo.fnParseString(-2, '.', Version), '') AS Cut
FROM	@Table

E 12°55'05.76"
N 56°04'39.42"

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-25 : 17:43:46

quote:

Originally posted by tkizer


SELECT CASE WHEN SecondPeriod = 0 THEN Column1 ELSE SUBSTRING(Column1, 1, CHARINDEX('.', Column1, CHARINDEX('.', Column1) + 1) - 1) END
FROM
(
	SELECT Column1, CHARINDEX('.', Column1, CHARINDEX('.', Column1) + 1) AS SecondPeriod
	FROM @t
) t

Tara Kizer
http://weblogs.sqlteam.com/tarad/

I like this one... but why can't I turn it into a function


alter function returndbo.VsSlim(@Version varchar)
returns varchar as
begin return CASE 
WHEN (CHARINDEX('.', @Version, (CHARINDEX('.', @Version) + 1))) = 0 
THEN @Version
ELSE SUBSTRING(
	@Version, 
	1,
	 CHARINDEX('.', @Version, CHARINDEX('.', @Version) + 1) - 1
) 

END

end

and get multiple digits?

---------
Ignorance is bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 17:53:14

Why would you want to turn it into a function? Are you going to be running it against multiple tables or multiple columns?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 17:55:37

At any rate, you need to provide sizes for your varchars otherwise it uses varchar(1).

create function VsSlim(@Version varchar(50))
returns varchar(50) as
begin return CASE
WHEN CHARINDEX('.', @Version, CHARINDEX('.', @Version) + 1) = 0
THEN @Version
ELSE SUBSTRING(
@Version,
1,
CHARINDEX('.', @Version, CHARINDEX('.', @Version) + 1) - 1)
END
END
go

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-25 : 18:18:05

quote:
Originally posted by tkizer

Why would you want to turn it into a function? Are you going to be running it against multiple tables or multiple columns?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Yes. And thanks for the tip.

---------
Ignorance is bliss, but knowledge is thrill.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources