MID, CHARINDEX OR SUBSTRING??

• LoosinMaMind

  Hall of Fame

  Points: 3366

  More actions

  March 30, 2011 at 3:47 am

  #234757

  Hi

  Simple one this, for people in the know. I simply don’t know the terminology of what I’m looking for here.

  I would like to split the file in table below by the ':' symbol.

  What is the syntax for getting everything LEFT and everything RIGHT of the ':' symbol into a seperate field.

  For rowkey 1 I would like to be able to see ARMPMAEWC in 1 field and ARMPM in another.

  Thanks

  SAMPLE DATA

  CREATE TABLE PS_TestForOnline

  (

  rowkey INT,

  CLINIC VARCHAR (25),

  )

  INSERT INTO PS_TestForOnline

  VALUES('1','ARMPMAEWC:ARMPM' );

  INSERT INTO PS_TestForOnline

  VALUES('2','ACPAED:AC');

  INSERT INTO PS_TestForOnline

  VALUES('3','RCELDC:RC');

  INSERT INTO PS_TestForOnline

  VALUES('4','LEEDMECCH:LEED');

  select * from PS_TestForOnline

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  March 30, 2011 at 4:49 am

  #1305330

  Try this:-

  select substring(clinic,1,charindex(':',clinic)-1),

  substring(clinic,charindex(':',clinic)+1,LEN(clinic))

  from PS_TestForOnline

• LoosinMaMind

  Hall of Fame

  Points: 3366

  More actions

  March 30, 2011 at 6:14 am

  #1305382

  Many Thanks for that. Works a treat.