none
Update part of the string

    Question

  • Hello,

    please, I have million of records and I need to replace LAST THREE characters, regardless of what they are,  in column that is always of the same length e.g.,1234abc need to be:

    1234000

    i.e., to keep first four characters!

    thanks

    Thursday, September 04, 2014 12:11 PM

Answers

  • yes, well, just create an update statement like this:

    UPDATE table set column = stuff(column, 5,3,'abc')

    that is if you want to keep the first four characters and the entire string is 7 chars long.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    • Marked as answer by daredavil3011 Thursday, September 04, 2014 12:41 PM
    Thursday, September 04, 2014 12:33 PM
  • update yourtable 
    set ColumnName = Left(ColumnName,7)

    • Marked as answer by daredavil3011 Thursday, September 04, 2014 9:07 PM
    Thursday, September 04, 2014 8:36 PM
    Moderator
  • Let me make sure I understand what you are looking for.

    The column is always exactly 7 characters long?

    You always want to keep the 1st four characters?

    You always want to replace the last three characters with "000"?

    You have millions of rows to update?

    You can use either:

    update #tmpX set x = left(x,4)+'000'
    --or --
    update #tmpX set x = stuff(x, 5,3,'000') 
    
    I found that the using the left function slightly out performs the stuff on a table with 2,000,000 rows, but only by a few milliseconds.

    Thursday, September 04, 2014 9:05 PM

All replies

  • Hi,

    you can use this function:

    STUFF ( character_expression, start, length, character_expression )
    (see Books Online)

    "Start" would be len(@string)-3


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Thursday, September 04, 2014 12:22 PM
  • thanks but, may be I was not clear, it is a word about column in a table, not just string...
    Thursday, September 04, 2014 12:25 PM
  • yes, well, just create an update statement like this:

    UPDATE table set column = stuff(column, 5,3,'abc')

    that is if you want to keep the first four characters and the entire string is 7 chars long.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    • Marked as answer by daredavil3011 Thursday, September 04, 2014 12:41 PM
    Thursday, September 04, 2014 12:33 PM
  • create table test (col char(7))
    insert into test values('1234abc'),('1234xyz'),(null) 
    
    select left(col,len(col)-3)+'000' col  from test
    
    
    
    drop table test

    Thursday, September 04, 2014 12:35 PM
    Moderator
  • Works, thanks...

    bye

    Thursday, September 04, 2014 12:41 PM
  • Hi,

      If your column data type is of datatype varchar(max) or nvarchar(max)

      you can use the WRITE clause in the UPDATE statment

    DROP TABLE tblupdatewrite
    
    CREATE TABLE tblupdatewrite
    (
    c1 VARCHAR(MAX)
    )
    
    INSERT INTO tblupdatewrite
    SELECT '12345abc'
    
    UPDATE tblupdatewrite
    SET C1.WRITE('000',LEN(C1)-3,3)
    
    SELECT * FROM tblupdatewrite


    Best Regards Sorna

    Thursday, September 04, 2014 12:41 PM
  • Hi,

    You can do the following way.

    SELECT STUFF(columnname, len(columnname)-2, 3, '123') from <table name>;

    Note :- '123' -- you can replace it with your own value.

    After reviewing the result set you can go for updating the table.


    Thanks, RajaSekhara Reddy . K

    Thursday, September 04, 2014 12:44 PM
  • Hi,

    this is what he talked about. You have to du an Update-Statement with the use of STUFF:

    Update tablename Set rowname=STUFF(rowname,(len(rowname))-2,3,'000')

    Assuming rowname is never null and all rows have at least 3 chars. No where clause because you want to update all entries.

    Regards

    Sebastian



    Bodo: I think STUFF is zero based index so len-2 is right.
    • Edited by Sebastian_HH Thursday, September 04, 2014 12:54 PM
    Thursday, September 04, 2014 12:50 PM
  • Hi,

    a propos my previous question, if I do this:

    set ColumnName = STUFF(ColumnName, 8,1,'') in order to have deleted only the last character that is always on the 8th position, I got unwanted result? I have lost complete one value that was exactly 7 characters long and thus, was not suppose to be touched, at all!?

    What went wrong?

    Thursday, September 04, 2014 8:31 PM
  • update yourtable 
    set ColumnName = Left(ColumnName,7)

    • Marked as answer by daredavil3011 Thursday, September 04, 2014 9:07 PM
    Thursday, September 04, 2014 8:36 PM
    Moderator
  • I am going to check the result wright now.....some time...
    Thursday, September 04, 2014 8:49 PM
  • Thank you so, seems works...So simple, instead of deleting the last one , just leave first seven characters, and,

    that is !

    thank you indeed Mr.Jingyang

    Thursday, September 04, 2014 9:01 PM
  • Let me make sure I understand what you are looking for.

    The column is always exactly 7 characters long?

    You always want to keep the 1st four characters?

    You always want to replace the last three characters with "000"?

    You have millions of rows to update?

    You can use either:

    update #tmpX set x = left(x,4)+'000'
    --or --
    update #tmpX set x = stuff(x, 5,3,'000') 
    
    I found that the using the left function slightly out performs the stuff on a table with 2,000,000 rows, but only by a few milliseconds.

    Thursday, September 04, 2014 9:05 PM
  • Yes, both but, I have used the second (STUFF) option....It worked. But, when I tried to use, likewise, this:

    quote:

    set ColumnName = STUFF(ColumnName, 8,1,'') in order to have deleted only the last character that is always on the 8th position, I got unwanted result? I have lost complete one value that was exactly 7 characters long and thus, was not suppose to be touched, at all!?

    LEFT proposed by Mr. Jingyang, as much as I can see, works....i.e., instead of delete the last char, just leave left 7?

    Simple but, could not get it alone...

    Thanks to both of you colleagues and others for such prompt and clear answers just like it used to be on this forum. It is please to ask a question on this Forum, indeed!

    Thank you so, indeed...BYE

    Thursday, September 04, 2014 9:17 PM