Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    -27° 28' 22" , 153° 1' 22"
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Remove unnecessary spaces from sql server varchar field

    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?
    "Computers are considered female - As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories for it."

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    -27° 28' 22" , 153° 1' 22"
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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
    "Computers are considered female - As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories for it."

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    -27° 28' 22" , 153° 1' 22"
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    <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.
    "Computers are considered female - As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories for it."


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •