isolating a sub-string in a string before a symbol in SQL Server 2008

Question

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?

score 83 · Accepted Answer · answered Jul 25 '12 at 13:46

83

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

answered Jul 25 '12 at 13:46

LittleBobbyTables - Au Revoir

32,008
25
109
114

1

Should that - 2 be - 1? – MaxJ Sep 18 '18 at 10:51
1

no, there are spaces on both sides of the char '-' @MaxJ – pbordeaux Sep 19 '18 at 02:37
1

@pbordeaux - Ah, yes - missed that, apologies. Was not the case in what I required this for and overlooked that – MaxJ Sep 19 '18 at 15:45

score 7 · Answer 2 · edited Aug 09 '19 at 20:11

7

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

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

edited Aug 09 '19 at 20:11

johnnyRose

7,310
17
40
61

answered Jul 25 '12 at 15:04

NikRED

1,175
2
21
39

score 1 · Answer 3 · answered Feb 16 '21 at 05:36

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'))

isolating a sub-string in a string before a symbol in SQL Server 2008

3 Answers3

Linked

Related