Updating column in table - 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

Updating column in table

Author

Topic

MadManJ
Starting Member

3 Posts

Posted - 2010-05-20 : 16:11:49

Hi everyone, can some one help me with this. I need to be able to change the value in my table for one of the columns which has data like this:

Grand Rapids, MI 12588 and I need to get everything on the left of the comma and save just that back for each row in the table. Can someone show me how I would do this with like the Northwind or any other db then I code apply it to my db. I know this involves using LEFT() but I have no clue as to how to write a stored procedure that will update my table and do what I have described above. I'm using SQL Server 2008 Developers Edition if it matters.

Thanks in advance so much!

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 16:19:59

create table t1 (c1 varchar(50))

insert into t1 values('Grand Rapids, MI 12588')
insert into t1 values('San Diego, CA 92101')

--check this to make sure it'll work
select LEFT(c1, charindex(',', c1)-1)
from t1

--perform the data modification
update t1
set c1 = LEFT(c1, charindex(',', c1)-1)

--verify the data looks good
select * from t1

drop table t1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MadManJ
Starting Member

3 Posts

Posted - 2010-05-21 : 10:04:52

Thanks a lot for your help with this. The problem I am having is I am getting an error when I run the code below saying "Invalid length parameter passed to the LEFT or SUBSTRING function."

Code:
USE MCM

declare @String varchar(100);
declare @Result varchar(100);

IF (EXISTS(SELECT TOP 1 * FROM [dbo].[CertificateMaster_Destination]))
BEGIN
UPDATE [dbo].[CertificateMaster_Destination]

set [CertificateMaster_Destination] = LEFT(CertificateMaster_Destination, charindex(',', CertificateMaster_Destination)-1)

SET @Result = 'Success';
END

I am trying to figure out why I am getting this error.

Thanks,
MadJMan

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-21 : 11:00:24

It usually means you have values in your DB without the comma (,) and this results in the length being calculated as -1...which is invalid.

EDIT: So the question is, does your table have some data without the comma?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 12:14:57

Based on the error message and as explained earlier by vijay, the error occurs when the character used for splitting a string is not found.
In your case it is comma.

Just try the below code and let us know whether it works for you or not ?

Code:
USE MCM

declare @String varchar(100);
declare @Result varchar(100);

IF (EXISTS(SELECT TOP 1 * FROM [dbo].[CertificateMaster_Destination]))
BEGIN
UPDATE [dbo].[CertificateMaster_Destination]
set [CertificateMaster_Destination] =
LEFT(CertificateMaster_Destination,
CASE WHEN CHARINDEX(',',CertificateMaster_Destination)>0 THEN
CHARINDEX(',',CertificateMaster_Destination)-1
ELSE LEN(CertificateMaster_Destination)
END)

SET @Result = 'Success';
END

One more thing i noticed in your code is that both table name and column name are same. Though allowed by SQL engine but i feel it is not a good practice to use the same name for both table and column in same table.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

MadManJ
Starting Member

3 Posts

Posted - 2010-05-21 : 13:47:37

Hi, and thanks again for all the help. I tried your code and it failed but not on your end. It seems that this column is part of a multi colum index and has to be unique but seems like it still should be as I am not changing the column that is part of the key. I have to get it figured out before I can move forward. So as it appears it's possible to have data like this:

Destination ZCode
ORLANDO, FLORIDA ZZ2158
ORLANDO, FLORIDA DD2510

so when I select left I'm getting Orlano but the ZCode is not being changed so I am not sure why I get the duplicate key error.

Many Thanks,
James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources