locked
The first update of the words in a text field RRS feed

  • Question

  • Hi Friends ;

    I want to The first update of the words in a text field ;

    FIELD = 'JANUARY SERVICE FEE'

    I want to only update first words using variable

    I can already do follow code but I want to do using variable if possible

    UPDATE TABLE SET NAME=UPPER((SELECT datename(month,GETDATE()))) + ' SERVICE FEE' WHERE ID=61

    Thank you in advance


    Wednesday, February 19, 2014 10:01 PM

Answers

  • Create table test (id int,name varchar(100) )
    
    Insert into test values (61, 'JANUARY SERVICE FEE')
     
     
    select * from test
    
    
    Update test
    Set Name=UPPER(datename(month,GETDATE())) +''+substring(name,charindex(' ',name),len(name))
    --+ Stuff(name, 1, charindex(' ',name),'')  
    Where id=61
    
    select * from test
    
    
    
    drop table test

    • Proposed as answer by Fanny Liu Friday, February 21, 2014 12:07 PM
    • Marked as answer by Kalman Toth Saturday, March 1, 2014 8:27 PM
    Wednesday, February 19, 2014 11:31 PM

All replies

  • Can you show your Table structure (DDL) with some sample data? Thanks.
    Wednesday, February 19, 2014 10:08 PM
  • Doesn't matter , Both can be
    Wednesday, February 19, 2014 10:20 PM
  • Create table test (id int,name varchar(100) )
    
    Insert into test values (61, 'JANUARY SERVICE FEE')
     
     
    select * from test
    
    
    Update test
    Set Name=UPPER(datename(month,GETDATE())) +' '+ Stuff(name, 1, charindex(' ',name),'')  
    Where id=61
    
    select * from test
    
    
    
    drop table test

    Wednesday, February 19, 2014 10:26 PM
  • Firstable Thanks for qouickly respond ,

    I already can do 2 method ;

    1 . UPDATE TABLE SET NAME=UPPER((SELECT datename(month,GETDATE()))) + ' SERVICE FEE' WHERE ID=10

     2 . DECLARE @AY NVARCHAR(MAX)
    DECLARE @TEXT NVARCHAR(MAX)
    DECLARE @TABLE NVARCHAR(MAX)
    SET @AY=(SELECT UPPER(datename(month,GETDATE())))
    SET @TEXT=' SERVICE FEE ' 
    SET @TABLE=(SELECT SUBSTRING(FIELD,1,(CHARINDEX(' ',FIELD + ' ')-1)) FROM MYTABLE WHERE ID=10)
    UPDATE MYTABLE SET FIELD=@AY + @TEXT WHERE ID=10

    I want to update SUBSTRING(FIELD,1,(CHARINDEX(' ',FIELD + ' ')-1)=@AY But I think its not possible



    • Edited by Aly14 Wednesday, February 19, 2014 10:49 PM modified
    Wednesday, February 19, 2014 10:48 PM
  • Create table test (id int,name varchar(100) )
    
    Insert into test values (61, 'JANUARY SERVICE FEE')
     
     
    select * from test
    
    
    Update test
    Set Name=UPPER(datename(month,GETDATE())) +''+substring(name,charindex(' ',name),len(name))
    --+ Stuff(name, 1, charindex(' ',name),'')  
    Where id=61
    
    select * from test
    
    
    
    drop table test

    • Proposed as answer by Fanny Liu Friday, February 21, 2014 12:07 PM
    • Marked as answer by Kalman Toth Saturday, March 1, 2014 8:27 PM
    Wednesday, February 19, 2014 11:31 PM
  • Can I use the substring statement before update tag , thus ;

    NOT like this :    NAME=substring(name,charindex(' ',name),len(name))

    like this        :  

    first of words (NAME)   =  substring(name,charindex(' ',name),len(name)))

    Thursday, February 20, 2014 8:07 AM
  • Please let us know what would be your desired resultset in Li's solution?
    Thursday, February 20, 2014 8:15 AM
  • Bybirol,

    It would really help if you could let us know what text is to be converted to what - a bit clearly :)

    By assuming giving the below:

    --To replace 'january' with current month - 'february'
    UPDATE #test
    SET name=STUFF(name,1,charindex(' ',name),UPPER(DATENAME(MONTH,GETDATE()))+' ')
    WHERE id=61
    
    --To remove 'January '
    UPDATE #test
    SET name=STUFF(name,1,charindex(' ',name),'')
    WHERE ID=61


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Proposed as answer by Kalman Toth Saturday, March 1, 2014 8:28 PM
    Thursday, February 20, 2014 8:22 AM
  • May be the below?

    Create table test (id int,name varchar(100) )
    
    Insert into test values (61, 'JANUARY SERVICE FEE')
    
    select * from test
    
    Update test
    Set Name=Replace(Name,SUBSTRING(name,0,charindex(' ',name)+1),UPPER(datename(month,GETDATE()))+' ')
    Where id=61
    
    Select * From test
    
    drop table test

    Thursday, February 20, 2014 8:23 AM
  • See a generic solution here. you can use it to get any position word within a long delimited string (in this case space character)

    http://visakhm.blogspot.in/2013/05/get-nth-positioned-string-from.html



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Kalman Toth Saturday, March 1, 2014 8:28 PM
    Thursday, February 20, 2014 8:27 AM
  • Thanks for everybody WHO want to help me .

    I can do and solved like this ;

    DECLARE @AY NVARCHAR(MAX)
    DECLARE @TEXT NVARCHAR(MAX)
    DECLARE @TABLE NVARCHAR(MAX)
    SET @AY=(SELECT UPPER(datename(month,GETDATE())))
    SET @TEXT=' SERVICE FEE ' 
    SET @TABLE=(SELECT SUBSTRING(FIELD,1,(CHARINDEX(' ',FIELD + ' ')-1)) FROM MYTABLE WHERE ID=10)
    UPDATE MYTABLE SET FIELD=@AY + @TEXT WHERE ID=10

    Thank you for cooperation .


    • Edited by Aly14 Thursday, February 20, 2014 8:29 AM modified
    Thursday, February 20, 2014 8:28 AM
  • Thanks for everybody WHO want to help me .

    I can do and solved like this ;

    DECLARE @AY NVARCHAR(MAX)
    DECLARE @TEXT NVARCHAR(MAX)
    DECLARE @TABLE NVARCHAR(MAX)
    SET @AY=(SELECT UPPER(datename(month,GETDATE())))
    SET @TEXT=' SERVICE FEE ' 
    SET @TABLE=(SELECT SUBSTRING(FIELD,1,(CHARINDEX(' ',FIELD + ' ')-1)) FROM MYTABLE WHERE ID=10)
    UPDATE MYTABLE SET FIELD=@AY + @TEXT WHERE ID=10

    Thank you for cooperation .


    The below is not making any importance....

    SET @TABLE=(SELECT SUBSTRING(FIELD,1,(CHARINDEX(' ',FIELD + ' ')-1)) FROM MYTABLE WHERE ID=10)

    Why are you assigning to variable if you can do it on the fly?

    • Proposed as answer by Kalman Toth Saturday, March 1, 2014 8:28 PM
    Thursday, February 20, 2014 8:32 AM
  • I know , I can do write  directly,I already say to first post

    UPDATE TABLE SET NAME=UPPER((SELECT datename(month,GETDATE()))) + ' SERVICE FEE' WHERE ID=61

     But I practice on DECLARE variable .

    Thursday, February 20, 2014 8:43 AM
  •  >>>But I practice on DECLARE variable .

    So I think whatever update you are doing is not a best practice. Rather what you can do is just store the text (varchar)"Service Fee" in the database with associated date as a date field and during display in the app tier you do the string manipulations.

    I believe that would be more efficient too


    Satheesh
    My Blog | How to ask questions in technical forum


    • Proposed as answer by Kalman Toth Saturday, March 1, 2014 8:28 PM
    Thursday, February 20, 2014 2:31 PM