Thread: cleaning values

    #1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    11

    cleaning values


    is there a quick way to do this within sql.

    i am thinking of something along the lines of what would be an equivalent to something in PHP (obviously not a rdbms) like
    foreach ($ArrayName as $varName=>$value)
    {
    $value = rtrim(ltrim($value));
    }

    instead of having to do each variable manually.

    Thanks for the help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    One reason this will not work is because TSQL variables are strong typed. You would error out if you attempted this with a date or number variable.
    One option is to create a function that contains all the variables called resetVariables that does nothing but reset the variables.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    11
    thanks for the help. I was hoping there was a quick way.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    One reason this will not work is because TSQL variables are strong typed. You would error out if you attempted this with a date or number variable.
    Another reason is MS-SQL has no concept of arrays. You would have to use cursors and it would be slow and ugly, and like victor pointed out you would run into issues with strongly typed columns.

    If you are using varchar fields they will be automatically trimmed. If you are using char fields they will contain leading and trailing spaces.

    If you are storing variable width data use a varchar and trim it on the way in through a sproc. Then you do not need to trim them on the way out. Otherwise just trim the columns on the way out in a select..
    Code:
    select ltrim(rtrim( nme ) ) from dbo.fooBar where id = 1
    You can also update a table and trim the values, however this will do no good if they are stored type char.

    Code:
    create table fooBar(
        id int identity(1,1) primary key,
        stat varchar(10) check (stat IN('Retired', 'Playing', 'Injured') ),
        nme varchar(10),
        foo int
    )
    go
    
    create procedure insert_foobar( @stat varchar(10), @nme varchar(10), @foo int )
    as
    begin
        insert into dbo.fooBar ( stat, nme, foo ) values( ltrim(rtrim(@stat)), ltrim(rtrim(@nme)), @foo )
    
    end
    go
    Last edited by abombss; January 20th, 2004 at 01:20 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    I might have misunerstood your question. You can do a mass update to all the rows in a table with an update query.
    Code:
    update dbo.fooBar set stat = ltrim(rtrim(stat))
    This will trim all the rows in the column named stat in the fooBar table.Careful... If you do not use a where clause you could update some data you don't want to.The following example uses a where clause to limit the update to rows with a col1 value of 3.

    Adam
    Code:
    update dbo.fooBar set col2 = ltrim(rtrim(col2)), col3 = upper(col3)
    where col1 = 3

IMN logo majestic logo threadwatch logo seochat tools logo