adding a word as suffix to an existing value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

adding a word as suffix to an existing value

Author

Topic

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-17 : 06:18:38

Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 06:55:34

Can you post some sample data and expected result?

Madhivanan

Failing to plan is Planning to fail

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-17 : 07:26:03

thanks for your response madhivanan, in 'id' field there are three values like 'suresh', 'madhivanan' and 'someone'. if i tried to insert 'suresh' again into the id field it should add '-rev' word to 'suresh' and it should save in the field as 'suresh-rev'. i hope this is what you asked me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 07:42:46

IF EXISTS (SELECT * FROM Table1 WHERE ID = @NameToInsert)
UPDATE Table1 SET ID = ID + '-rev' WHERE ID = @NameToInsert

INSERT Table1 (ID) VALUES (@NameToInsert)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 07:43:24

IF EXISTS (SELECT * FROM Table1 WHERE ID = @NameToInsert)
SET @NameToInsert = @NameToInsert + '-rev'

INSERT Table1 (ID) VALUES (@NameToInsert)

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 08:30:54

quote:
Originally posted by sureshprabhu

thanks for your response madhivanan, in 'id' field there are three values like 'suresh', 'madhivanan' and 'someone'. if i tried to insert 'suresh' again into the id field it should add '-rev' word to 'suresh' and it should save in the field as 'suresh-rev'. i hope this is what you asked me.

Well. If you store comma seperated values in a column, then you need to considering following

http://databases.about.com/od/specificproducts/a/normalization.htm

to make the code easier

Madhivanan

Failing to plan is Planning to fail

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-17 : 11:57:12

Thanks Peso and madhivanan, my problem solved. Thanks alot.

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-17 : 16:14:07

If want to add a incremental value instead of a word, how can i? In clear, before this I added '-rev' word to the existing value, now I want to add incremental value. I have 'suresh' already as a value if tried to insert it again it should inert as 'suresh-1', again if i tried to insert it it should insert 'suresh-2'. How can I do this? Please help me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 16:28:07

[code]DECLARE @Items INT

SELECT @Items = COUNT(*)
FROM Table1
WHERE ID = @NameToInsert

IF @Items > 0
SET @NameToInsert = @NameToInsert + '-' + CONVERT(VARCHAR, @Items)

INSERT Table1
(
ID
)
VALUES (
@NameToInsert
)[/code]

E 12°55'05.25"
N 56°04'39.16"

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-17 : 17:48:56

Thanks Peso, its working. But still I didnt get what I want as I am not clear. Now, I am facing this problem - first I inserted 'suresh', second time when I tried to insert 'suresh' it inserted 'suresh-1', upto this its working. But if I tried to insert 'suresh' again its inserting again 'suresh-1', at this type of situation I want to insert 'suresh-2'. But, if I tried to insert 'suresh-1' its inserting 'suresh-1-1', this is ok. I want the value to be unique. Please help me. Thanks again for giving answers. I will be very thankful to your patience.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 17:52:56

[code]DECLARE @Items INT

SELECT @Items = COUNT(*)
FROM Table1
WHERE ID LIKE @NameToInsert + '-[0-9]%'
OR ID = @NameToInsert

IF @Items > 0
SET @NameToInsert = @NameToInsert + '-' + CONVERT(VARCHAR, @Items)

INSERT Table1
(
ID
)
VALUES (
@NameToInsert
)[/code]

E 12°55'05.25"
N 56°04'39.16"

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-17 : 19:12:25

Can I replace the 9 with a bigger value than that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 19:16:00

NO!
If you know LIKE, you would know "[0-9]" is for looking for "xxxx-1" to "xxxx-9" WITH MORE CHARACTERS padded!

E 12°55'05.25"
N 56°04'39.16"

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-18 : 07:19:30

Thanks alot Peso, This is exactly what I want.

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-24 : 09:47:08

if i want to do nothing when one value already exists in the database..then what i have to do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 10:10:25

Do nothing!

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-24 : 10:18:00

quote:
Originally posted by Peso

Do nothing!

E 12°55'05.25"
N 56°04'39.16"

Genius!

Future guru in the making.

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-24 : 10:20:41

can i get command please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 10:38:16

If exists(......)
--
else
--do your stuff

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources