CODEWITHSUNDEEP
sqlsql-server-2008
Red Devil
Red Devil

Reputation: 2393

substring function in sql

I have a table color and values are like this

Color   
D-E   
D-015   
E-0157  
WWW-014     
FER-014     
T-015

I am expecting the output like

Color
D-E   
D   
E   
WWW     
FER    
T

If i try 

select substring(color,1,1) from Color

it is giving me

Color 

D    
D   
E   
W   
F   
T

How to get the expected output.

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You seem to want to get everything up to the first hyphen (assuming that 'D-E' as a desired result is a typo):

select left(color, charindex('-', color) - 1)

If you want to be safe, in case the color does not have a hyphen:

select left(color, charindex('-', color + '-') - 1)

If you actually want everything before the first hyphen followed by a number, use patindex():

select left(color, patindex('%-[0-9]%', color + '-0') - 1)

Upvotes: 1

Related Questions