SQLTeam.com | Weblogs | Forums

SQL substring question

#1

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

#3

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

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

Its working Thank you very much

#6

Great