Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can we use regular expression to select the item from database?

The table item is like below

Table column|name|
10.01.02    | a  |
100.2.03    | b  |
1021.10.04  | c  |

Now my problem is that i need to select the code and get the substring like below

Table column|name|
10.01       | a  |
100.2       | b  |
1021.10     | c  |

any suggestion with regular expression or substring?

share|improve this question
Use SUBSTRING_INDEX() – ypercubeᵀᴹ Aug 30 '13 at 10:00
@ypercube substring_index() not work in mssql? – ANonmous Change Aug 30 '13 at 10:11
You had MySQL tag before. No, I don't know of equivalent function in SQL-Server. – ypercubeᵀᴹ Aug 30 '13 at 10:12
@ypercube sorry for my mistake,could you find any idea regarding this problem – ANonmous Change Aug 30 '13 at 10:14

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')

  , 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.


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,

  , case CHARINDEX('.', data, 1) -- find first dot
    when 0 then 'No dot'
        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)
  , name
from stringtest
share|improve this answer
Nice but it will give error is there is a value without 2 dots. – ypercubeᵀᴹ Aug 30 '13 at 11:37
@ypercube Well, what result do you want to have if there is only one dot? You can't expect complete answers to incomplete questions... – TToni Aug 30 '13 at 12:31
Of course, there are regex CLR implementations if that is actually necessary. Such as,, etc. – RLF Aug 30 '13 at 21:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.