November 2, 2007 at 10:16 am
I have a field that is type char(9). When I run a package it inserts a 6 digit number into that field. I want to pad that field with 3 0's. I tried this update statement:
UPDATE table1
SET field = '000' & field
This fails and the error is
The data types varchar and char are incompatible in the boolean AND operator
Any ideas how I can accomplish this?
November 2, 2007 at 11:07 am
The answer is in the Error Msg. & is boolean operator. Replace with + (plus) sign. Add logic to pad with zeros based on lenght when len is variable. If the field's len is variable you may also need to consider changing the type to varchar.
November 4, 2007 at 10:52 am
IF it's a number, you might need to CAST it as a character string as well before combining it with a plus (+) and the zeros.
November 5, 2007 at 4:42 am
to be sure i would recommend the following statement, that way your sure you always end up with 9 characters, if you input is less than 6 for some reason.
UPDATE table1
SET field = CAST(RIGHT('000000000' + CAST(field as varchar(9)),9) AS CHAR(9))
by padding the left with zeros and pulling out the right 9 you are sure to always have 9 characters, the rest just makes sure sql converts to the proper types for concatenation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply