Red Devil Red Devil - 2 months ago 20
SQL Question

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.

Answer

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)