How to separate date from a string?

问题:

Hi i have the string llike,

"on 01-15-09 witha factor of 0.8"

i wanted to seperate this string in the follwing way,

1] date as 01-15-09 2] Factor of 0.8

NOTE : String length is not fixed.

so how can we seperate the data in the form of #1 & #2 ?

回答1:

To get the date you can use PATINDEX().

declare @yourString varchar(100)
set @yourString = 'on 01-15-09 with a factor of 0.8'

select substring(@yourString,
            patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%', @yourString),
            8)

To get "factor of xx" you can do:

select substring(@yourString,
        patindex('%with a%', @yourString) + 7,
        20)

回答2:

declare @txt varchar(max)
set @txt = 'on 01-15-09 witha factor of 0.8'

select cast(substring(@txt, patindex('% [0-9][1-9]-%', @txt), 9) as date) [date], 
cast(right(@txt, patindex('%_ %', reverse(@txt))) as decimal(9,1)) Factor

Result:

date       Factor
---------- ------
2009-01-15 0.8