#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0

    Removing white space from list of names


    I work for a school district and we have a need of exporting data from our SQL 2005 database. Often a student’s first or last name may contain a space. Unfortunately, the software into which we are importing the data does not allow for white space. After a bit of research I found the REPLACE command.

    For example SELECT REPLACE (@NewFirstName,‘ ‘,’’) But this variable only works for a single name. How do I tell SQL to find the space and replace it with no space for a whole list of student names?

    Thank you in advance -
    David
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,248
    Rep Power
    4279
    Originally Posted by walkerSGUSD
    How do I tell SQL to find the space and replace it with no space for a whole list of student names?
    exactly the same way, except with a column like students.student_name, instead of a variable like @NewFirstName

    also, you have to use single quotes (next to the enter key on most keyboards) rather than those wonky microsoft "smart quotes" (a misnomer if ever there was one)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0
    Thank you, Rudy, for your reply. That makes sense. However, when I run this
    SELECT replace (STU.FN, ' ', '')

    (i did use single quotes)
    SQL responds with
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "STU.FN" could not be bound.

    Do you have any suggestions? thanks again -
    David
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,248
    Rep Power
    4279
    Originally Posted by walkerSGUSD
    Do you have any suggestions?
    i'd have to see the entire sql statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0
    that was it ! :-)
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,248
    Rep Power
    4279
    Originally Posted by walkerSGUSD
    that was it ! :-)
    you mean this? --
    Code:
    SELECT replace (STU.FN, ' ', '')
    it seems you've omitted the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0
    whoops - my mistake, much better now :-) thank you again

IMN logo majestic logo threadwatch logo seochat tools logo