Substring?

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  June 26, 2009 at 6:30 am

  #207682

  Hi everybody, i have a column with values looking like this 'pp_uniline_usd:Entity', how do i remove the ':Entity' part of the string?

  e.g

  this...

  'pp_uniline_usd'

  instead of this...

  'pp_uniline_usd:Entity'

  Merci:-)

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  June 26, 2009 at 6:39 am

  #1015968

  sorry people, being very silly today...

  just used the REPLACE function:w00t:

• Paul White

  SSC Guru

  Points: 150467

  More actions

  June 26, 2009 at 6:39 am

  #1015970

  Usually, one would use a combination of the following SQL features:

  LEFT or SUBSTRING

  CHARINDEX or PATINDEX

  REPLACE or STUFF

  See String Functions

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• t.hitendra

  SSCommitted

  Points: 1620

  More actions

  June 26, 2009 at 6:50 am

  #1015976

  Hi,

  If ':Entity' is common among all the values then

  select left(columnname,len(substring(columnname,1,charindex(':',extra)-1))) from tablename

  please try this

  create table #chk(colm varchar(30))

  insert into #chk

  select 'pp_uniline_usd:Entity' union all

  select 'dfsdafsdfsdaf:Entity' union all

  select 'dfsdafsdfsdaf:Entity' union all

  select 'ffff:Entity'

  select left(colm,len(substring(colm,1,charindex(':',colm)-1))) from #chk

  drop table #chk

  I hope i'm close enough