Re: HoH keys with trailing white spaces ????

So my theory is that the data is improperly defined on the database itself, which leads to all these subtle nuances.

My guess is that the fields in the table are of type CHAR(N). This pads strings that are too short. You could alter the table so the fields become VARCHAR(N). I don't think the change in schema will remove trailing spaces, but it will prevent spaces to be added the next time.

Comment on Re: HoH keys with trailing white spaces ???? Select or Download Code

Replies are listed 'Best First'.
Re^2: HoH keys with trailing white spaces ???? by 3dbc (Monk) on Dec 10, 2011 at 21:19 UTC
`select column_name, data_type, character_maximum_length from informati +on_schema.columns where table_name = 'grasshopper' and column_name like '%id%'` [download] id char 10	[reply] [d/l]
Re^3: HoH keys with trailing white spaces ???? by JavaFan (Canon) on Dec 10, 2011 at 21:43 UTC
You sound like a grasshopper, who prefers to be a grasshopper all his life. I have of course no idea whether changing the type of the column benefits your company. And with your attitude, neither will you. What's the worst that can happen? That you find out why the column has type CHAR(N) instead of VARCHAR(N)?	[reply]
Re^4: HoH keys with trailing white spaces ???? by 3dbc (Monk) on Dec 12, 2011 at 22:56 UTC
Mr Starbucks, What if the existing application (.net, ASP etc.) has been built to consume the char column as is, will changing to varchar blow up the entire system all because we don't want some stupid spaces in the default HoH select (without chopblanks set), which may or may not conform to MSSQL datatype best practices? Maybe I should pitch rebuilding the entire system because the foundation is crooked??? If so, now you're starting to sound like a dentist....	[reply]