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 |
|
folettra
Starting Member
15 Posts |
 Posted - 2006-11-28 : 11:34:55
|
| I have a column in one of my tables that needs to be used as part of a compound key for that table. The compound key is made up of 4 fields.key_charTypeNameRecord_numberThe problem with the name field is that there may be two records with the same name (one in upper case and the other lower case)example is ITSC_RESOLVE and ITSC_Resolve. These two records are treated as the same.To fix this I changed the collation on this name column to be "SQL_Latin1_General_Cp437_CS_AS"This change fixed this problem but now I have another one.Some of the names have a trailing space.example is "ITSC_Resolve" vs. "ITSC_Resolve "This extra space is preventing me from creating the compound key.The source system treats these two record as unique but SQL does not.What can I do about this? |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-11-28 : 12:27:41
|
| Clean you data.Trim the trailing spaces.STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
 |
|
|
folettra
Starting Member
15 Posts |
Posted - 2006-11-28 : 14:02:50
|
| I can not trim the trailing spaces. That is the way the data comes from the source system. The source system distinguishes between spaces and no spaces so I have to deal with the spaces.Is there a way to make sql treat the spaces as an extra character so that the two values are considered different?Do you think I can convert the space at the end of the name to something else that sql will recognize?By the way the source system is Peregren Service Center and the records are the clocks if that helps. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-11-28 : 15:08:02
|
| Well, there are a couple of hack solutions I can think of.Store the string as the reverse of itself with the REVERSE() function. Then your trailing spaces become leading spaces and are preserved.Or tack on a Period "." at the end of your value, converting your trailing spaces into imbedded spaces.STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 15:30:32
|
| Or change space ASCII(32) to hard spaces ASCII(160)Peter LarssonHelsingborg, Sweden |
|
|
|
folettra
Starting Member
15 Posts |
Posted - 2006-11-28 : 15:40:06
|
| Thanks, I will give your suggestions a try. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-11-28 : 16:30:36
|
| Another approach could be to store the source system value as just an attribute and associate your own internal id to each source system value. Use your internal id as part of your composit key.Be One with the OptimizerTG |
|
|
|
|
|
|
Values in the primary key have trailing spaces