[RESOLVED] Sql Server Columns Update Issue!

Hi :wave:

I have database table with two columns, I want to update some values by adding a text at the beginning and at the end of these values!?

For example:
Table name: food
Column 1 name: food id
Column 2 name: plant

Original values in the table before adding text update:

[food id] [plant]
--------------------------
[1] [APPLE FR]
[2] [BANANA FR]
[3] [TOMATO FR/VEG]


Values in the table after adding text update:

[food id] [plant]
--------------------------
[1] [GRANNY SMITH APPLE FRUIT]
[2] [BLUEFIELD BANANA FRUIT]
[3] [CHERRY TOMATO FRUIT/VEGETABLE]

Note: there are spaces between the values in the column

Anybody help me please with update scripts:confused:
Regards…:)

You can do something as simple as this:

update food
set plant = 'GRANNY SMITH APPLE FRUIT'
where plant = 'APPLE FR'

or if you know exact id then this:

update food
set plant = 'GRANNY SMITH APPLE FRUIT'
where food_id = 1

Quote:

---

Originally Posted by RhinoBull

You can do something as simple as this:

update food
set plant = 'GRANNY SMITH APPLE FRUIT'
where plant = 'APPLE FR'

or if you know exact id then this:

update food
set plant = 'GRANNY SMITH APPLE FRUIT'
where food_id = 1

---

thanks RhinoBull for your help:)
But I'm looking to how add text to the values not replacing them
Any idea please...

update food
set plant = 'GRANNY SMITH ' + plant + 'UIT'
where food_id = 1

Quote:

---

Originally Posted by brucevde

update food
set plant = 'GRANNY SMITH ' + plant + 'UIT'
where food_id = 1

---

Thanks brucevde :wave:

the scripts works fine...:thumb:

if possible could you please script case "food id 3" :o

Regrads...:)

Couldn't you lift the code from brucevde's sample and modify it to suit your need? Just replace the value in the WHERE criteria.

Quote:

---

Originally Posted by dee-u

Couldn't you lift the code from brucevde's sample and modify it to suit your need? Just replace the value in the WHERE criteria.

---

I’ve try it, but I couldn’t know how solve it :confused:
Because the adding in the middle of the values!! My level of programming in SQL is beginner:(

So show us what you tried and why it is wrong

Quote:

---

Originally Posted by GaryMazzone

So show us what you tried and why it is wrong

---

update food
set plant = 'CHERRY TOMATO' + plant + 'UIT'/'ETABLE'
where food_id = 3

What is it you want to write here? You are trying to do a division on strings that is not possible!

maybe:

sql Code:

update food
set plant = 'CHERRY TOMATO' + plant + 'UIT / ETABLE'
where food_id = 3

Quote:

---

Originally Posted by GaryMazzone

What is it you want to write here? You are trying to do a division on strings that is not possible!

maybe:

sql Code:

update food
set plant = 'CHERRY TOMATO' + plant + 'UIT / ETABLE'
where food_id = 3

---

I’m really confused, sorry but I didn’t understand what you mean…

As I have said I’m newbie in the SQL …

Anyway thanks for your support :)

What you wrote means

Set plant = 'CHERRY TOMATO' + plant + 'UIT'/'ETABLE'

That the string plant Add CHERRY TOMATO before the value in plant then add UIT after the value of plant.

Next in the statment is a division (/) then the string 'ETABLE' which mean that the value 'CHERRY TOMATO' + plant + 'UIT' (which is a string) and divide it by the value 'ETABLE' (which is also a string). That is not allowed.

I assume (and of course I can be wrong here) you want to and the string
'UIT / ETABLE' after the current value in the plant field.

Is that correct?

You wrote -

'UIT' / 'ETABLE' in you code. The / character is the Division character for dividing numbers (4 / 2 = 2) so you would in effect be trying to divide words.

Try this instead -

UPDATE food
SET Plant = 'CHERRY ' + left(Plant,9) + 'UIT/' + right(Plant,3) + 'ETABLE'
WHERE food_id = 3

OK I just looked at you first post as to what you want that is more dificult

CHERRY TOMATO FRUIT/VEGETABLE is what you want and what you have is
TOMATO FR/VEG

What is the database you are using to store the data in it will make a difference in how you do this.

You will need to add CHERRY to the first part of the string then use a substring to add the UIT then andother substring to add the ETABLE to the ending

Quote:

---

Originally Posted by NeedSomeAnswers

You wrote -

'UIT' / 'ETABLE' in you code. The / character is the Division character for dividing numbers (4 / 2 = 2) so you would in effect be trying to divide words.

Try this instead -

UPDATE food
SET Plant = 'CHERRY ' + left(Plant,9) + 'UIT/' + right(Plant,3) + 'ETABLE'
WHERE food_id = 3

---

That’s what I’m looking for :thumb:
Many thanks NeedSomeAnswers :)

Quote:

---

Originally Posted by GaryMazzone

OK I just looked at you first post as to what you want that is more dificult

CHERRY TOMATO FRUIT/VEGETABLE is what you want and what you have is
TOMATO FR/VEG

What is the database you are using to store the data in it will make a difference in how you do this.

You will need to add CHERRY to the first part of the string then use a substring to add the UIT then andother substring to add the ETABLE to the ending

---

I appreciate your effort and time for helping… :)

Best regards :wave:

No Problem :D