Removing all charachters after the last '.'

Question

Post reply

Removing all charachters after the last '.'

zubairm76

SSC Journeyman

Points: 90
More actions
February 17, 2009 at 2:56 am

#130981

Hi,
I have a column named code in a table called data with the following sample information:
1
1.1
1.1.1
1.1.10
1.1.11
1.1.12
I need a query that would remove all information after the last '.'
My result table should read:
1
1
1.1
1.1
1.1
1.1
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

Try this

SELECT SUBSTRING(s, 1, LEN(s) - CHARINDEX('.', REVERSE(s)))

FROM

(

SELECT '1' as s

UNION ALL SELECT '1.1'

UNION ALL SELECT '1.1.1'

UNION ALL SELECT '1.1.10'

UNION ALL SELECT '1.1.11'

UNION ALL SELECT '1.1.12'

) AS TmpTable

Regards,
Nitin

zubairm76 SSC Journeyman Points: 90 More actions · Answer 2

Thanks for the reply,

Problem is that I have hundreds of rows of data and I cant type every row in.Also the rows aren't only 1.1, there is also 1.2, 1.2.1, 1.3, 1.3.1, 1.3.2 etc.

Thanks again

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 3

🙂

Inner table(with UNION ALL) is for example only. You can use your table inplace of TmpTable..

something like this

Select SELECT SUBSTRING(yourColumnName, 1, LEN(yourColumnName) - CHARINDEX('.', REVERSE(yourColumnName)))

FROM yourTableName

Regards,
Nitin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Is there a maximum number of dots?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

zubairm76 SSC Journeyman Points: 90 More actions · Answer 5

Thanks Alot for the help,works like a dream.

Apologies for the misunderstanding,

Thank you :hehe:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

-- If the number of dots is never greater than 3, then this will work:

DECLARE @String varchar(20)

SET @String = '123.45.678.90'

SELECT REPLACE(@String+'~', PARSENAME(@String+'~',1), '')

Result:

123.45.678.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden