Auto added space in field while updating table in database

UPDATE member SET first Name = RTRIM(first Name); Over 5 years since the question was asked and it seems none of the current answers will actually remove 100% of whitespace from the start and end of a string.

As mentioned in other posts, the default s would be needed - and in the general case this could go on indefinitely.

Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

Recently, while doing some development work for one of my clients, I was given the requirement to add Created and Modified timestamps to a whole bunch of existing data tables.

These tables were referenced by hundreds of different My SQL queries, and to complicate matters further, we were in the middle of migrating the code over to a new database library.

I know its already accepted, but for thoses like me who look for "remove ALL whitespaces" (not just at the begining and endingof the string): select SUBSTRING_INDEX('1234 243', ' ', 1); returns '1234' Thank you for your interest in this question.

Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

I didn’t much fancy rewriting all of the SQL queries, potentially twice, just to add a couple of timestamps, so modifying code wasn’t much of an option.

Thankfully, a couple of My SQL’s internal features came to the rescue.This statement will remove and update the field content of your database To remove whitespaces in the left side of the field value UPDATE table SET field1 = LTRIM(field1); ex.UPDATE member SET first Name = LTRIM(first Name); To remove whitespaces in the right side of the field value UPDATE table SETfield1 = RTRIM(field1); ex.Would you like to answer one of these unanswered questions instead?--Just change table name declare @My Table varchar(100) set @My Table = 'My Table' --temp table to get column names and a row id select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a My SQL database.

Tags: , ,