Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
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
add comment

1 Answer 1

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
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 msdn.microsoft.com/en-us/magazine/cc163473.aspx, codeproject.com/Articles/85954/Use-RegEx-in-SQL-with-CLR-Procs, etc. –  RLF Aug 30 '13 at 21:09
add comment

Your Answer

 
discard

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.