Updating a field

Question

Post reply

Updating a field

rconway-574047

Mr or Mrs. 500

Points: 522
More actions
August 19, 2008 at 9:32 am

#192505

Hello all,
I am using SQL Server 2000 and I have what I think is a fairly simple question but I can't quite figure this out. I have a table named tbl1847 and a field within that table defined as tablename: [1], datatype: text.
I need to add two leading 0's in the text that is already in the field. So, if the existing data is this:
19847
201A3
I need this:
0019847
00201A3
Any help is greatly appreciated.
Ron

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

try this code to update

Update tbl1847

Set YourFieldName = '00' + YourFieldName

I was unsure what your field name was, so subititue it in..

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

update tablename set colname = '00' + colname

Edit:

oops you beat me too it

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

rconway-574047 Mr or Mrs. 500 Points: 522 More actions · Answer 3

Okay, the field name is [1] and the datatype is text. Here is the query that I wrote:

UPDATE tbl1847

SET tbl1847.[1] = '00' + tbl1847.[1]

I get this error:

Invalid operator for data type. Operator equals add, type equals text.

Thanks

Ron

steveb. SSC-Forever Points: 46733 More actions · Answer 4

you are using the text data type so you will need to cast to varchar to convert

UPDATE tbl1847

SET tbl1847.[1] = '00' + cast(tbl1847.[1] as varchar(max))

I would not recommend using the text data type unless n you need to store more than 8000 characters , you are better off using varchar.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Is there a reason that you need the datatype TEXT for this column? I believe that Microsoft is depreciating that in favor of the newwer VARCHAR(MAX) datatype. TEXT is very limited in what functions can be used with it. If you absolutely need to use TEXT datatype, then try this:

update tablename set

columnname = '00' + SUBSTRING(columnname,1,DATALENGTH(columnname))

rconway-574047 Mr or Mrs. 500 Points: 522 More actions · Answer 6

Thank you, that was the ticket. I had to change the varchar(MAX) to varchar(10) though. I got an error when I tried it with MAX.

I took this database over from someone else and will look at why he thought he had to use text for the field.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

You got an error with VARCHAR(MAX) because that is only valid in SQL 2005 and 2008.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

OK, sorry for the confusion with VARCHAR(MAX). Since this question was in the SQL Server 2005 forum, I went with that. In 2000 you can have VARCHAR be up to 8000, so it should work for you unless any of your DATALENGTHs in that TEXT column are longer than 8000.