Post reply

trim blank spaces permanently

  • July 24, 2009 at 6:31 am

    #134149

    Hi,

    I have a table and every column has blank spaces to the left and right side of the content.

    I tried the ltrim and rtrim functions but they only temporarily remove these spaces in expr1 and expr2. I need to get rid of these spaces permanently. Is there a simple way to do this? In fact could I achieve it on an entire table?

    Thanks for any advise.

    SELECT RTRIM(Student_Forename) AS Expr1, RTRIM(Surname) AS Expr2

    FROM wce_ilr

  • July 24, 2009 at 6:36 am

    #1029605

    update wce_ilr set student_forname= ltrim(rtrim(Student_ForeName)), surname = ltrim(rtrim(surname))....

    Do something like that for each column. Let me know if this works

  • July 24, 2009 at 6:36 am

    #1029606

    it sounds like you have two issues;

    first issue, if you have trailing spaces that won't go away,

    I'm thinking that the table wce_ilr has two columns Student_Forename and Surname with a datatype of CHAR and not VARCHAR.

    to fix it permenantly, you'd need to alter the table definition;you can use the GUI to change it, and that would be the easiest, or you can do it via a TSQL command:

    an example of that syntax would be

    ALTER TABLE wce_ilr ALTER COLUMN Student_Forename VARCHAR(100) --is that the right size?

    second issue,

    if you have preceeding spaces, you'd need to update the data:

    UPDATE wce_ilr

    SET Student_Forename = LTRIM(Student_Forename),

    Surname = LTRIM(Surname)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply