May 12, 2011 at 3:29 am
Hi
I like to know how to slit input like 3.6.1 to 3.6 in sql 2005...
Thanks in advance
Seshu
May 12, 2011 at 5:35 am
Not that I think it makes any difference, but you have posted this in the SQL Server 7, 2000 forum. I assume you meant the 2005 forum.
The problem is to find the location of the last '.' in the string. It is generally easier to find the first occurrence in a string, so why not use the REVERSE function and look for the first '.'?
Once you know where the location of the '.', you can subtract this value from the length (LEN) of the string to work out how many characters you need.
Then you can use the LEFT function to truncate the string, as you now have the length and the position of the last '.'.
And if you only want to strip the lasts '.' where there are more than one, use CHARINDEX to check how many there are:
DECLARE @test-2 varchar(20)
SET @test-2 = '1.2.3';
IF CHARINDEX('.', @test-2) > 1
PRINT LEFT((@test), LEN(@test) - PATINDEX('%.%',REVERSE(@test)))
Tested in 2005/8R2, I haven't used 7 or 2000 for a long, long time.
BrainDonor.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply