在sql server 2008中拆分字符串

In my table, I have Experience field like

在我的表中,我有经验领域

Experience

0to0Years
2to0Years
7to12Years

Here I want to split into

在这里,我想分成几个

Yearfrom - 0

年 - 0

Yearto- 0

How to split strings here. I search so many articles. I can't find the correct solution. Is there any way to do here?

如何在这里拆分字符串。我搜索了这么多文章。我找不到正确的解决方案。这有什么办法吗?

4 个解决方案

#1

Here is a working solution

这是一个有效的解决方案

declare @yearfrom varchar(2),@yearto varchar(2)

select @yearfrom=substring('0to0Years',0,patindex('%to%','0to0Years')),
 @yearto=substring('0to0Years',patindex('%to%','0to0Years')+2,patindex('%Years%','0to0Years')-patindex('%to%','0to0Years')-2)

SqlFiddle: http://www.sqlfiddle.com/#!3/d41d8/12483

For working on your column replace '0to0Years' with column name

要处理列,请使用列名替换“0to0Years”

    declare @yearfrom varchar(2),@yearto varchar(2)

        select @yearfrom=substring(col_name,0,patindex('%to%',col_name)),
         @yearto=substring(,patindex('%to%',col_name)+2,patindex('%Years%',col_name)-patindex('%to%',col_name)-2)
from table_name where <condition>

#2

Try with following Steps...

尝试以下步骤......

                            --Create Table :
                            Create Table #Table
                            (
                            Name Varchar(50),
                            Experience Varchar(20)
                            )
                            Go

                            -- Insert Values :
                            Insert into #Table Values('Tom','0to0Years')
                            Insert into #Table Values('Victor','0to1Years')
                            Insert into #Table Values('Mark','11to12Years')
                            Go

                            --View Data
                            Select  * from #Table

                            --Using CharIndex  and Substring :
                            Select Name,
                            Substring(Experience,1,CharIndex('to',Experience)-1) as Yearfrom,
                            Substring(Experience,(CharIndex('to',Experience)+2),Len(Replace(Experience,'Years','')) - (CharIndex('to',Experience)+1)) as YearTo
                            from #Table

#3

Please try:

select 'YearFrom - '+substring(Data, 0, PatIndex('%[to]%', Data)) YearFrom, 
    'YearTo - '+replace(stuff(Data, 1, PatIndex('%[to]%', Data)+1, ''), 'Years', '') YearTo
from
(
    Select '0to0Years' Data union
    Select '2to0Years' Data union
    Select '756to12Years' Data
)x

#4

Try this using Parsename function

使用Parsename功能尝试此操作

Select  parsename(replace(Experience,'to','.'),2) ,
        substring(parsename(replace(Experience,'to','.'),1),0,
        charindex('Y',parsename(replace(Experience,'to','.'),1))) 
from YourTable

Demo in SQLFiddle

在SQLFiddle演示

#1