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 |
|
jones_d
Yak Posting Veteran
61 Posts |
 Posted - 2004-08-18 : 06:07:54
|
| Hi,I have 2 databases on 2 different machines and they are both running the same stored procedures and loading the same data. I noticed that one of the databases is handling the data differently to ther other. For example, in 1 table I have a field setup as char, length 3 and NULLs are allowed. The data I load for this particular record does't put anything in this field.When I select this record from each database - nothing is displayed - as expected. However, in one database when I use the following function LEN(LTRIM(RTRIM(curcode))) the result returned is 0 and when I use the same function in the other database the result returned is NULL.NULL is actually the result I would prefer in this situation because that is what is returned in our production database. I am trying to get the test database to behave the same as the production system.Does anyone know why the result of the above function differs in each database? Is there anyway I can get it to return null?Any ideas or help would be much appreciated.Thanks |
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2004-08-18 : 06:43:43
|
| Correction to the original posting: -It is actually the LTRIM(RTRIM(curcode)) function applied to the same field that is returning 2 different results It is retuning blank or nothing in one databaseand it is returning NULL in the otherThe LEN function is affected by the value returned by this function. |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 10:17:52
|
| The length of null is null, but the length of an empty string = 0. You have said that the data is the same in both db's but have you re-verified that it is?It sound like one db contains the value null and the other db contains an empty string ''. |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2004-08-18 : 11:50:12
|
| Hi,The data in both fields is the same. In the meantime I have come across this website:http://doc.ddart.net/mssql/sql70/sp_da-di_2.htmAccording to this site the LTRIM and RTRIM functions will return different values depending on the compatibility level setting for the database. View return values of LTRIM and RTRIM under the compatibility level comparisons.We are using SQL Server 7 and I am trying to find out what the compatibility level is - not sure how to do this yet. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 13:48:57
|
| I don't think there are Compatibility Levles in SQL 7, I think that came in in SQL 2000 (not sure though).Might be that you have some differences in ANSI style settings.Just a guess though ...Kristen |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2004-08-18 : 14:54:28
|
| Hi,In order to get the compabibility level run the following SQL:select cmptlevel from master.dbo.sysdatabases where name = 'name_of_database'In the database from which I get NULLs the compatibility level is 65In the database from which I get blanks the compatibility level is 70Therefore I imagine that my problem is caused by the compatibility level. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 15:15:25
|
| Looks like itCompatibility level setting of either 60 or 65:LTRIM(' ') returns NULL.LTRIM(N' ') returns NULL.RTRIM(' ') returns NULL.RTRIM(N' ') returns NULL.The concatenation of null yields null setting of sp_dboption is off (disabled) which returns an empty string if any operands in a concatenation operation is null.Compatibility level setting of 70 or 80:LTRIM(' ') returns an empty string.LTRIM(N' ') returns an empty string.RTRIM(' ') returns an empty string.RTRIM(N' ') returns an empty string.The concatenation of null yields null setting of sp_dboption is on (enabled), which returns a NULL if any operands in a concatenation operation is null.Kristen |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 15:18:59
|
| Interesting to see!! Thanks for posting the findings. |
|
|
|
|
|
|
LEN function returns NULL as length of field