substring function in sql

Question

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.

Because it is 1 type of color in system and cannot remove. And for D-015 is something we altered in the system. But while displaying the color it should only show D — Red Devil
– Red Devil, Commented Oct 6, 2016 at 2:52

Gordon Linoff · Accepted Answer · 2016-10-06 02:49:54Z

1

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)

answered Oct 6, 2016 at 2:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

I updated my question, i missed on 1 scenario. Basically i want to remove -015 from D-015. I want to remove the numbers from the color

I ran your query and fom D-E it is showing D-. I want it to show D-E and for D-015 it is showing D-

Yes your 3rd answer is correct. Thanx Gordon. patindex does the trick