Need to delete first character of a string

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  June 12, 2003 at 2:50 pm

  #159327

  Hello,

  In a table, I have a varchar(100) field called 'StreetName' in a table called 'tblStreetData' in which the actual street name record can ocassionally be prefixed with 'N', 'S', 'E', 'W' in order to designate a point of direction. The prefixes are always followed by a space and then the street name.

  I need to delete these prefixes and remove the space from such records. I just started working on this, and I think I need to use LTRIM with an UPDATE statement in some way.

  Any ideas?

  Thanks!

  CSDunn

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  June 12, 2003 at 3:18 pm

  #461152

  This example uses 788, 567, and 577 instead of N,S,E,W. Replace 4 & -4 with 2 & -2, and 5 with 3.

  USE pubs

  SELECT stor_address,

  CASE WHEN SUBSTRING(stor_address,0,4) IN('788','567','577') THEN SUBSTRING(stor_address,5,LEN(stor_address)-4) ELSE stor_address END

  AS CutAddress

  FROM stores

  Everett

  
  
  Everett Wilson
  ewilson10@yahoo.com

• jraha

  SSC Eights!

  Points: 817

  More actions

  June 12, 2003 at 3:20 pm

  #461153

  update tblStreetData

  set streetName =

  case when substring(a.streetName,1,2) = 'N ' then substring(a.streetName,3,len(a.streetName)-2)

  when substring(a.streetName,1,2) = 'S ' then substring(a.streetName,3,len(a.streetName)-2)

  when substring(a.streetName,1,2) = 'E ' then substring(a.streetName,3,len(a.streetName)-2)

  when substring(a.streetName,1,2) = 'W ' then substring(a.streetName,3,len(a.streetName)-2)

  else streetName

  end

  from tblStreetData a

  BUT... if the table is huge:

  I would avoid doing an update. I would try to do an insert into a new table. drop the old table and then rename the new table. Something like that.

  insert into newTable ()

  select

  case when substring(a.streetName,1,2) = 'N ' then substring(a.streetName,3,len(a.streetName)-2)

  when substring(a.streetName,1,2) = 'S ' then substring(a.streetName,3,len(a.streetName)-2)

  when substring(a.streetName,1,2) = 'E ' then substring(a.streetName,3,len(a.streetName)-2)

  when substring(a.streetName,1,2) = 'W ' then substring(a.streetName,3,len(a.streetName)-2)

  else streetName

  end as streetName,

  [other fields here]

  from tblStreetData a

  drop table tblStreetData

  ???

  master.dbo.sp_rename [dbName].dbo.newTable [dbName].dbo.tblStreetData

  oops... I was beat to it.

  Edited by - jraha on 06/12/2003 3:21:20 PM

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  June 12, 2003 at 3:27 pm

  #461156

  Thanks for your help!

  CSDunn

• mccork

  SSC-Insane

  Points: 22288

  More actions

  June 12, 2003 at 6:08 pm

  #461184

  Noting that you say they only occasionally contain the prefixes, another option that will only issue updates against rows that fit the criteria is:

   
  
  update tblStreetData
  
      set StreetName = SUBSTRING(StreetName, 3, LEN(StreetName)-2)
  
      where StreetName LIKE '[NEWS] %'
  
  

  
  Cheers,
  - Mark