SQLTeam.com | Weblogs | Forums

White Spaces


#1

How to avoid white spaces in saving your data into Sql Server using PHP ? Aside from TRIM Function.


#2

whatr's wrong with LTRM(RTRIM(data))?


#3

Why you don't use regular expressions to remove excessive white-space.

preg_replace('/(\s)+/', ' ', $string);
Reference: php removing excess whitespace

PHP does not edit any_POST or _GET values. It keeps it exactly the way it was received.


#4

I don't think the database should do this either - I would store what the APP sends me.

That said, I would write an SProc that trims whitespace, which an APP could explicitly use/call, but I don't think I would have, say, a trigger that did that(i.e. routinely for every single "save" to the database).

We remove trailing spaces in the APP at the point where data entry occurs that might, erroneously, include them. The user is then aware that the change has been made (although a SPACE is very hard to see! but using the Cursor it is detectable on a data entry form).

But that's how we have always done it, and there may be other schools of thought. I just would hate to see every INSERT and UPDATE statement surround every VARCHAR column with Ltrim(Rtrim(@MyValue)) if 99% of the time those functions will find no work to do, and thus just waste CPU.

We do have to use Ltrim(Rtrim()) in processes that pull data from other systems - if they don't care about such things and that data has erroneous leading / trailing spaces.


#5

How about a scheduled Cleanup Job?

UPDATE MyTable
SET MyColumn = LTrim(Trim(MyColumn))
WHERE    MyColumn LIKE ' %'
      OR MyColumn LIKE '% '

#7

Thanks for the help.... very big help