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
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
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
;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
SELECT string, CASE WHEN string NOT LIKE '%.%.%' THEN string
ELSE LEFT(string, CHARINDEX('.', string + '.', CHARINDEX('.', string + '.') + 1) - 1) END
FROM #data
Its working Thank you very much
Great