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.
| Author |
Topic |
|
Chirs_P
Starting Member
2 Posts |
 Posted - 2007-04-27 : 11:44:08
|
The ERP system we use fills NCHAR fields with spaces rather than nulls. I need to move selected tables/records/fields to another database (still MS-SQL 2000), and need to move the true values of the fields.However, the 3rd party software that I am using apparently checks the length of the source fields, and doesn't try to populate destination fields if the source is 'empty'. Since MS_SQL reports the length of NCHAR fields filled with blank (chr(32)) as 0, I am getting a lot of NULLs in my destination table.For example:SELECT DRKY, LEN(DRKY) AS LEN_DRKY, DRRT, drsyFROM TESTCTL.F0005WHERE (DRRT = N'01') AND (DRKY = N' ')DRKY LEN_DRKY DRRT DRSY 0 01 00 0 01 01 0 01 03C 0 01 06 0 01 09 0 01 14 0 01 15 0 01 15L Is there a setting or something I can do to force MS-SQL to report the actual length of the data in DRKY? (Spaces are characters too!)Thanks,Chris |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-04-27 : 11:46:32
|
| use datalength() instead of len()_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Chirs_P
Starting Member
2 Posts |
Posted - 2007-04-27 : 15:35:49
|
[quote]Originally posted by spirit1
use datalength() instead of len()Thank you very much. That indeed gives me the correct answer.Now if only I can convince the 3rd party software provider to use it!ChrisP |
|
|
|
|
|
|
Len( <nchar field containing spaces> ) returns 0??