Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 982
CarbonD1225 Avatar asked Jul 25 '12 13:07

CarbonD1225


People also ask

How do I extract text before a specific character in SQL?

Using the charindex function allows you to search for the @ sign and find its starting position and then the substring is used to extract the characters before the @ sign.

How do I extract a string after a specific character in SQL Server?

The SUBSTRING() extracts a substring with a specified length starting from a location in an input string. In this syntax: input_string can be a character, binary, text, ntext, or image expression. start is an integer that specifies the location where the returned substring starts.


3 Answers

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
like image 125
LittleBobbyTables - Au Revoir Avatar answered Oct 11 '22 19:10

LittleBobbyTables - Au Revoir


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

SELECT SUBSTRING(@dd, 1, CHARINDEX('-', @dd) -1) F1,
       SUBSTRING(@dd, CHARINDEX('-', @dd) +1, LEN(@dd)) F2
like image 36
NikRED Avatar answered Oct 11 '22 20:10

NikRED


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'))
like image 24
rchacko Avatar answered Oct 11 '22 18:10

rchacko