The first update of the words in a text field

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

Answer 1

Can you show your Table structure (DDL) with some sample data? Thanks.

Answer 2

Doesn't matter , Both can be

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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)))

Answer 7

Please let us know what would be your desired resultset in Li's solution?

Answer 8

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'>

Answer 9

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

Answer 10

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

Answer 11

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 .

Answer 12

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?

Answer 13

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 .

Answer 14

 >>>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