Remove unnecessary spaces from sql server varchar field

[JustAsking]

I have a online system which users enter in information into text boxes. The information is stored in an mssql server database. As users enter in their information they are leaning on the space bar somehow and the result is having too many spaces between the words. I was wondering if there is a sql function/query I can execute to remove these unwanted spaces? Also, the online system is coded using ASP, can I remove the unwanted spaces between words before inserting/updating to the mssql server table?

[raf]

you can do it inside ASP by using the Trim() function

but are you sure that the spaces aren't added by MsServer? I don't know MsServer that well, but there are dataformats that fill up certain columntypes with spaces, until the specified length.

if the Trim() doesn't solve it, then check your columntype and the documentation for it.

[Roy Sinclair]

Originally Posted by JustAsking

the result is having too many spaces between the words.

(emphasis added)

You can use the "split" function to break the input up into an array and then use the 'join" function to reassemble it and if the space character is specified as the delimiter for both functions it should result in a string with a maximum of only one space between each word. This should also be possible with a regular expression but that's something I would need to study more before I could give you the right expression. Perhaps someone else with more recent experience with them could help since using a regular expression would make for cleaner code.

[raf]

Ha yes, i missed that. sorry.

But i don't really understand it. Either it's an accidental thing that doesn't frequently happen (i don't suppose you change all possible other typo's) so why specifically change this? or either it occurs frequently, and then there's probably something wrong with your formlayour or so ...

[JustAsking]

Originally Posted by raf

Either it's an accidental thing that doesn't frequently happen (i don't suppose you change all possible other typo's) so why specifically change this? ...

Its really only for reporting purposes, the management don't like reading the reports with a bunch of spaces between words. As you said, other typo'sz cabn't bew stopped

I will try using the split function and then join the words for the mean time, while researching how to use a regular expression for future reference.

Thanks

[JustAsking]

<script language="javascript" type="text/javascript">
<!--

/*
     Remove extra spaces from within a string
     and replace them with ONE space 
*/

str4 = "This     is     a     test!";
str4 = str4.replace(/\s+/g," ");
document.write(str4);
// -->
</script>

What do you know, once I identified what to search for (regular expressions) it was right here on the forum posted by the guru Whammy.