1

In my table, I have Experience field like

Experience

  • 0to0Years
  • 2to0Years
  • 7to12Years

Here I want to split into

  • Yearfrom - 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?

Improve this question
7
  • what to save it in DB or just disply? Commented Apr 16, 2013 at 5:35
  • i want to use select statement now. so it is just display means ok.then i execute another table. Commented Apr 16, 2013 at 5:36
  • 1
    ok fine, wait for a while. Commented Apr 16, 2013 at 5:37
  • you will have to use substring function , i am also working on it for this query. Commented Apr 16, 2013 at 5:42
  • ok. I use substring(exp,start,end) Commented Apr 16, 2013 at 5:43

4 Answers 4

Reset to default
3

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

    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>
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This one is good. But i have some problem when i use select insert query like, select top(5)'insert jobs()values('+ substring(Experience,0,patindex('%to%',Experience))*365*24*60*60 , substring(Experience,patindex('%to%',Experience)+2,patindex('%Years%',Experience)-patindex('%to%',Experience)-2)+')' from requirementsdetailsfororganization and getting error "Conversion failed when converting the varchar value 'insert jobs()values(' to data type int."
0

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
Improve this answer

Comments

0

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
Improve this answer

Comments

0

Try this using Parsename function

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

Improve this answer

1 Comment

did u find one issue there? when execute this query, result is for oto12years. so it will give 0 12y. If i remove that place means problem in 0to0years. "0" is not coming.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.