substring charindex not working

Question

DATA:

PFP_110
PFP_111
IFC_NP
IFC

Expected Output:

PFP
PFP
IFC
IFC

  select MemberName,
  SUBSTRING(MemberName,CHARINDEX('_', MemberName),4) from 
  SRC_TEST

but not getting the expected output

Thanks in Advance

what about LEFT? More, note that SUBSTRING has 3 arguments and you are passing the _ character as start and 4 characters as length. That means you will get 4 characters at the right of _ — B3S
– B3S, Commented Jun 6, 2018 at 9:02

Lukasz Szozda · Accepted Answer · 2018-06-06 09:01:59Z

3

You could use LEFT:

select MemberName,LEFT(MemberName,CHARINDEX('_', MemberName + '_')-1) 
from SRC_TEST;

answered Jun 6, 2018 at 9:01

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Thanks a lot.select MemberName, REPLACE(SUBSTRING(MemberName,1,CHARINDEX('', MemberName)),'',''), * from EXTRACT_FUND_DIMENSION_FROM_ESSBASE

charan · Accepted Answer · 2018-06-06 11:26:34Z

0

select MemberName, REPLACE(SUBSTRING(MemberName,1,CHARINDEX('', MemberName)),'',''), * from SRC_TEST

answered Jun 6, 2018 at 11:26

charan

29