Column data change - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Column data change

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-06-08 : 00:53:24

Dear All,

In my database table i have one column 'Code'. In that code column
nuber series is there as follows,

V001900...............V002000

In that series i want to change V. Means instead of V i want to show S as follows,

S001900...............S002000

Can anybody suggest me how to change ?

Harish

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 01:06:24

check with this select then update

select 's'+substring(code,2,len(code)) from table

update table set code='s'+substring(code,2,len(code))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-08 : 01:16:05

[code]
update table
set code=replace(code,'V','S')
where left(ltrim(code),1) = 'V'
and convert(int,replace(code,'V','')) between 1900 and 2000
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-06-08 : 01:50:09

Dear Freind,

I have tried ur queary but i am not getting wat exactly should do ?

I want to change column data 'S000001'....'S002000' instead of 'V000001'....'V002000'

For that u sends me quearies in both queary 'S' is there.
And i tried ur update queary i get following error,

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'OCRD_PRIMARY'. Cannot insert duplicate key in object 'dbo.OCRD'.
The statement has been terminated.

Can u plz tell me wat to do ?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 01:54:07

Hey nothing wrong in both the quires.

If the column is primary key. you can't duplicate the values.

For example:

You try to change the value from S000001 to V000001
if the value V000001 already in the column it raise the above error!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-06-08 : 02:07:53

Dear Freind,

I think u didn't get my question.

In my column series is there
V000001
.
.
V002000

I want replace this seriese name
S000001
.
.
S002000

Means i want replace V number series to S number series.

Thnkas

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 02:11:30

quote:
Originally posted by hspatil31

Dear Freind,

I think u didn't get my question.

In my column series is there
V000001
.
.
V002000

I want replace this seriese name
S000001
.
.
S002000

Means i want replace V number series to S number series.

Thnkas

Try this...

update table set code='V'+substring(code,2,len(code))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-06-08 : 02:19:41

Thnks very much webfred.
I solved my problem with ur queary.
Thnks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-08 : 02:33:27

welcome