Sql Server doesn't really support regular expressions too well. If all you need is to pick a substring from the beginning to the second dot, combine LEFT()
and CHARINDEX()
, like so:
-- Test table with data
create table stringtest(data varchar(32), name char(1))
insert into stringtest values('10.01.02', 'a')
insert into stringtest values('100.2.03', 'b')
insert into stringtest values('1021.10.04', 'c')
select
data
, left(data, CHARINDEX('.', data, CHARINDEX('.', data, 1)+1)-1) as 'subdata'
, name
from stringtest
-- Output:
10.01.02 10.01 a
100.2.03 100.2 b
1021.10.04 1021.10 c
The extraction
left(data, CHARINDEX('.', data, CHARINDEX('.', data, 0)+1)-1) as 'subdata'
works as follows. First, the inner CHARINDEX('.', data, 0)
will find the 1st dot. As index is 0, it starts from the beginning of the string. +1 will point to next character after the dot and pass that as the starting index to the outer CHARINDEX()
. The outer will find index of the second dot. Finally, LEFT()
will return a substring from zero to the 2nd dot.
Edit
As per ypercube's request, let's handle some errors. First, add some data with invalid values:
insert into stringtest values('1021', 'd')
insert into stringtest values('1021.10', 'e')
And let's tune the query to include nested case statements like so,
select
data
, case CHARINDEX('.', data, 1) -- find first dot
when 0 then 'No dot'
else
case CHARINDEX('.', data, CHARINDEX('.', data, 1)+1) -- find second dot
when 0 then 'No two dots'
else left(data, CHARINDEX('.', data, CHARINDEX('.', data, 1)+1)-1)
end
end
, name
from stringtest
SUBSTRING_INDEX()
– ypercube Aug 30 at 10:00