Update Part of a SQL field with charindex

• duncanlarry

  SSC Veteran

  Points: 270

  More actions

  June 23, 2014 at 12:45 pm

  #287143

  Hello,

  I have a field and the data is in the format below:

  12-FLA-2-02

  I am trying to update just the first part of the data before the first dash which is 12 to another field that is a single numeric value.

  update table set table.field = LEFT(table.field, CHARINDEX('-', table.field)-1) where table.field is null

  I am trying to manipulate this script below, but I cannot figure out how to update just the 12 before the dash.

  Thanks

  Larry

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  June 23, 2014 at 1:15 pm

  #1724170

  Read up on STUFF .. http://msdn.microsoft.com/en-us/library/ms188043.aspx

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• duncanlarry

  SSC Veteran

  Points: 270

  More actions

  June 23, 2014 at 2:39 pm

  #1724199

  Thank You!!!

  That STUFF function did exactly what I needed it to do.

  Thanks Again.

  

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  June 24, 2014 at 1:52 am

  #1724245

  You can use REPLACE function as well.

  declare @str as table( val varchar(20))

  insert @str

  select '12-FLA-2-02'

  declare @update varchar(20)='11111'

  select * from @str

  update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

  select * from @str

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 24, 2014 at 2:04 am

  #1724247

  Sachin Nandanwar (6/24/2014)

  ---

  You can use REPLACE function as well.

  declare @str as table( val varchar(20))

  insert @str

  select '12-FLA-2-02'

  declare @update varchar(20)='11111'

  select * from @str

  update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

  select * from @str

  You can, but I wouldn't recommend it:

  declare @str as table( val varchar(20))

  insert @str select '12-FLA-2-02'

  insert @str select '12-FLA-2-12'

  declare @update varchar(20)='11111'

  select * from @str

  update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

  select * from @str

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  June 24, 2014 at 2:42 am

  #1724249

  You can, but I wouldn't recommend it:

  declare @str as table( val varchar(20))

  insert @str select '12-FLA-2-02'

  insert @str select '12-FLA-2-12'

  declare @update varchar(20)='11111'

  select * from @str

  update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)

  select * from @str

  Yes makes sense.Thanks for pointing that out..

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server