dropdownlist values and text need trimmed

I have a dropdownlist which is being populated by a sqldatasource and when I look at the HTML created afterwards there's a lot of spaces in the value and text. This must because the length of the fields in the database is 50.

e.g.

The HTML is like this

        <select name="DropDownList1" onchange="javascript:setTimeout('__doPostBack(\'DropDownList1\',\'\')', 0)" id="DropDownList1" style="height:23px;width:148px;">
    <option value="IT  Dev                                           ">IT  Dev                                           </option>
    <option value="Marketing                                         ">Marketing                                         </option>
    <option value="HR                                                ">HR                                                </option>
    <option selected="selected" value="Finance                                           ">Finance                                           </option>
    <option value="Corporate                                         ">Corporate                                         </option>
    <option value="IT Support                                        ">IT Support                                        </option>

</select>

The controls source is:

        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
            DataValueField="dept_name" Height="23px" Width="148px">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>

        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:scConnString %>"
            SelectCommand="SELECT dept_name FROM depts"></asp:SqlDataSource

Can I trim the values in my sql, or the items in the dropdownlist after it has been populated?

Any开发者_Go百科 ideas?

You can't trim fixed length data. It'll always be padded back out to 50.

So CAST then trim

SELECT RTRIM(CAST(dept_name AS varchar(50))) AS dept_name FROM depts

--other options, after comment
SELECT RTRIM(CAST(dept_name AS varchar(50))) dept_name FROM depts
SELECT dept_name = RTRIM(CAST(dept_name AS varchar(50))) FROM depts

Or fix it in the client code

Change the data type To VarChar or nVarChar to allow varible length of data.

Depending on how your data is extracted, you may be able to trim on either end. For the second option (after population), you can use the OnDataBound event and iterate through the items to trim them.

I would recommend trimming in your SQL:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:scConnString %>"
        SelectCommand="SELECT LTRIM(RTRIM(dept_name)) FROM depts"></asp:SqlDataSource

trim spaces from sql:

SELECT ltrim(rtrim(dept_name)) FROM depts