Wednesday, September 10, 2014

Removing Spaces From SQL Server Data

Have you ever found yourself with spaces in SQL fields that just shouldn’t have spaces, such as usernames.  Well, this is an easy problem to fix with the SQL command "CHARINDEX".  CHARINDEX will return the first occurrence of an expression (such as a space) in a field.

So, you might say:


SELECT CHARINDEX(' ','Rats live on no evil star')

and what is returned is: 5 — the index of the first space in this string (a Palindrome, by the way.)  So, you can use this, along with SUBSTRING, to give you a final string with no spaces in it.  Here is an example:


SELECT UserName,
SUBSTRING(username, 1,CHARINDEX(' ',username)-1)+SUBSTRING(username,CHARINDEX(' ',username)+1,LEN(username)) AS NoSpaceUsername
FROM users
WHERE  CHARINDEX(' ',username)>0

This will yield results, such as: 
UserName       NoSpaceUsername
DE SILVA       DESILVA
DEL REY        DELREY
DEL TORO       DELTORO

I hope this is helpful!

No comments:

Post a Comment