[RESOLVED] Sql Server Columns Update Issue!

[HOTFIX]

Hi

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

[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

[HOTFIX]

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

[brucevde]

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

[HOTFIX]

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

Thanks brucevde

the scripts works fine...

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

Regrads...

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

[HOTFIX]

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
Because the adding in the middle of the values!! My level of programming in SQL is beginner

[GaryMazzone]

So show us what you tried and why it is wrong

[HOTFIX]

So show us what you tried and why it is wrong

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

[GaryMazzone]

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

maybe:

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

[HOTFIX]

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

maybe:

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

[GaryMazzone]

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?

[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

[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

[HOTFIX]

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
Many thanks NeedSomeAnswers

[HOTFIX]

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

[NeedSomeAnswers]

No Problem