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 |
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
 Posted - 2004-07-12 : 19:09:26
|
| Hey all-I've got a job that is bringing over large amounts of data from an older DB into SQL. The older DB fields are all CHAR(X) and this is what I defined in SQL also. I am having problems getting good data back from queries because all data is marked with whitespace. For example...Char(8)always has a value regardless of what is there.I thought about changing all fields to VarChar(X) and then doing a Rtrim on the data when it comes over?I guess I am looking for away to set all the blank fields to null and also trim the others.Any help would be great!Thanks,Doug |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-12 : 19:35:03
|
| Just a thought:You could create a procedure that iterates through the INFORMATION_SCHEMA.COLUMNS view for all CHAR fields and trims them...Open CursorFetch Next from CursorWhile Fetch Status <> 0 Update <table> SET <field> = RTrim(<field>) Fetch Nextetc etcHow are you bringing the data across?If it's by DTS, you could always set the source query to trim the columns for you. But you'd need to change all the CHAR's to VARCHAR's before you start.HTH,Tim |
 |
|
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
Posted - 2004-07-12 : 19:45:48
|
| Can I do something like this....INSERT INTO MYTABLE(E_ID,E_DEPT,E_ETC..)SELECT RTRIM(LTRIM(OTHERTABLE.E_ID)),RTRIM(LTRIM(OTHERTABLE.E_DEPT)),RTRIM(LTRIM(OTHERTABLE.E_ETC))FROMOTHERTABLE |
|
|
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
Posted - 2004-07-12 : 19:53:16
|
| OK, but if I do this I will have some rows records that respond to FIELD='' and others that are inserted by another program with NULL values. These will not query the same. Even if I convert the field to Varchar.... |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-12 : 20:38:02
|
| You could always do:CASE RTRIM(LTRIM(<field>)) WHEN '' THEN NULL ELSE RTRIM(LTRIM(<field>))A bit long-winded, but it should work.As long as you're consistent in your approach, you shouldn't have too many problems with querying '' or NULLs |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-12 : 21:27:54
|
| NULLIF(Value,'')will return NULL if VALUE is equal to ''.Thus, you can say NULLIF(RTRIM(LTRIM(Value)),'') and that will trim all whitespace on both sides and return NULL if the final result is ''.I've never seen another use for NULLIF other than this purpose !- Jeff |
|
|
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
Posted - 2004-07-13 : 09:36:15
|
| WOW! Thanks Jeff-I started to rewrite my import query using what me a timmy cooked up, but I think I will use what you added!Thanks again... |
|
|
|
|
|
|
|
|
Null and white Space are killing me!