isolating a sub-string in a string before a symbol

问题:

i am trying to extract a substring(everything before a hyphen, in this case) from a string as shown below:

Net Operating Loss - 2007
Capital Loss - 1991
Foreign Tax Credit - 1997

and want the year and name(substring before hyphen) separately, using SQL server Management studio 2008. Any advice? or idea how i can achieve this?

回答1:

DECLARE @test nvarchar(100)

SET @test = 'Foreign Tax Credit - 1997'

SELECT @test, left(@test, charindex('-', @test) - 2) AS LeftString,
    right(@test, len(@test) - charindex('-', @test) - 1)  AS RightString

回答2:

DECLARE @dd VARCHAR(200) = 'Net Operating Loss - 2007';

SELECT SUBSTRING(@dd, 1, CHARINDEX('-', @dd) -1) F1,
       SUBSTRING(@dd, CHARINDEX('-', @dd) +1, LEN(@dd)) F2