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?
This question is related to
sql-server
sql-server-2008
tsql
substring
This can achieve using two SQL functions- SUBSTRING and CHARINDEX
You can read strings to a variable as shown in the above answers, or can add it to a SELECT statement as below:
SELECT SUBSTRING('Net Operating Loss - 2007' ,0, CHARINDEX('-','Net Operating Loss - 2007'))
DECLARE @dd VARCHAR(200) = 'Net Operating Loss - 2007';
SELECT SUBSTRING(@dd, 1, CHARINDEX('-', @dd) -1) F1,
SUBSTRING(@dd, CHARINDEX('-', @dd) +1, LEN(@dd)) F2
Source: Stackoverflow.com