Problem with Update statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Update statement

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2007-11-02 : 12:38:15

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?

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 12:43:44

You need "+" instead of "&" for string concatenation. You may also need to prevent it running over the 9 character size.

SET field = LEFT('000' + field, 9)

Kristen

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2007-11-02 : 12:50:21

Awesome, thanks Kristen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 12:56:04

or if you wanted padding UPTO three zeros use the right function
SET field = RIGHT('000' + field, 9)
123456789 > 123456789 left makes it 000123456
12345678 > 012345678
1234567 > 001234567
123456 > 000123456

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 13:39:06

"if you wanted padding UPTO three zeros use the right function"

Oops! that's what I meant, thanks.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 13:42:17

Actually, maybe it wasn't! There's the whole issue that this is CHAR datatype and will be space padded.


DECLARE @MyString CHAR(9)

SET @MyString = '123456'

SELECT	LEFT('000' + @MyString, 9) AS [Left],
	RIGHT('000' + @MyString, 9) AS [Right1],
	RIGHT('000' + RTRIM(@MyString), 9) AS [Right2]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources