SQLTeam.com | Weblogs | Forums

SQL substring question

could you please help in getting the below sub string.

I have values like
1.1
1.10.1
1.2.2.1

expected output (need to exclude the digits after the second dot)
1.1
1.10
1.2

hi

please see if this what you want !!! hope this helps :slight_smile: :slight_smile:

please click arrow to the left for Drop CREATE Sample Data
drop table #data 
go 


create table #data 
(
string varchar(100)
)
go 


insert into #data select '1.1'
insert into #data select '1.10.1'
insert into #data select '1.2.2.1'
go 

select * from #data 
go

image

;with cte as 
(
	SELECT 
		  string
		, CHARINDEX('.',string)+1 FirstIndexof 
		, CHARINDEX('.',string, (CHARINDEX('.',string)+1)) SecondIndexof 
	from 
	   #data 
)
select 
      string 
	, substring(string,1,case when SecondIndexof =  0 then FirstIndexof else SecondIndexof -1 end)
from 
  cte 

image

SELECT string, CASE WHEN string NOT LIKE '%.%.%' THEN string
    ELSE LEFT(string, CHARINDEX('.', string + '.', CHARINDEX('.', string + '.') + 1) - 1) END
FROM #data
1 Like

Its working Thank you very much

Great