Update Part of a SQL field with charindex

  • 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

  • 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
  • Thank You!!!

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

    Thanks Again.

    ๐Ÿ˜€

  • 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

  • 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

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply