• Post Categories

  • Browse Blogs

  • Blog Stats

    • 307,795 hits
  • Syndications

    SQLServerPedia Contributor

T-SQL: Identifying, inserting and removing spaces in strings

Frequently, when working with strings you will need to identify, insert or remove spaces before, after or in between characters.

For example, you may want to show customer full names  by concatenating the columns that hold the different parts of customer names like FirstName, MiddleName, and LastName, separating each name part with a blank space. You may also want to remove spaces before, after or in between characters from email or website addresses. It is important to understand the different string functions available for you in T-SQL that may help you accomplish any of the tasks mentioned above:

Using single-quotes with space in between  ‘ ‘ versus char(32) to insert spaces between characters

Example #1:

DECLARE @FirstName varchar(25), @LastName varchar(25)
SET @FirstName = ‘Jose’
SET @LastName = ‘Chinchilla’
SELECT @FirstName + ‘ ‘ + @LastName — (one space between single-quotes)
–Result=> Jose Chinchilla

Example #2:

SELECT @FirstName + char(32) + @LastName — (single space special character represented by char(32))
–Result=> Jose Chinchilla

From these two examples we can observe that we get the same results by using single quotes with an empty space in between and char(32). The reason is because char(32) is the ASCII code value of a space character in T-SQL.

SELECT ASCII(‘ ‘)
Result=> 32
SELECT CHAR(ASCII(‘ ‘))
Result=> _  — (the underscore character ‘_’ represents a blank space for clarification purposes, no actual underscores will show)
SELECT CHAR(32)
Result=> _ — (the underscore character ‘_’ represents a blank space for clarification purposes, no actual underscores will show)

Using LEN vs DATALENGTH to get the number of characters in a string

Example #1:

DECLARE @FullName varchar(25)
SET @FullName = ‘ Jose Chinchilla ‘ — (notice spaces before and after the full name)
SELECT LEN(@FullName) –Result=>16

LEN will only count leading spaces and spaces in between the string but will not count trailing spaces.

Example #2:

SELECT DATALENGTH(@FullName)
–Result=>17

DATALENGTH will count spaces in between and both leading and trailing spaces as well.

Using LTRIM and RTRIM to remove leading and trailing spaces

DECLARE @FirstName varchar(25)
SET @FirstName = ‘ Jose ‘; — (notice space before and after the word Jose)
SELECT @FirstName
–Result=> _Jose_  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show) 
SELECT LTRIM(@FirstName)
–Result=> Jose_  –(only leading space is removed, the underscore character ‘_’ represent a blank space for clarification purposes, no actual underscore will show)
SELECT RTRIM(@FirstName)
–Result=> _Jose  — (only trailing space is removed, the underscore character ‘_’ represent a blank space for clarification purposes, no actual underscore will show)
SELECT LTRIM(RTRIM(@FirstName))
–Result=> Jose  — (both leading and trailing spaces are removed)

By nesting LTRIM and RTRIM you get similar results as the TRIM function in Excel and other programming languages. Unfortunately, there is no TRIM function in T-SQL.

Using CHARINDEX to find the position of all spaces in a string

In order for CHARINDEX to work properly you need to declare the data type length. For example: DECLARE @EmailAddress varchar(100) instread of DECLARE @EmailAddress  varchar. The same applies to nvarchar, char and nchar. You may use varchar(max) and nvarchar(max).

It is important to understand that CHARINDEX will only return the position of the first instance of the character you are looking for, in this case a space or char(32). To continue looking for the rest of the positions where you have empty spaces in your @EmailAddress variable you would need to loop through the entire string until no more spaces are found.

DECLARE @EmailAddress varchar(100), @SpacePositions varchar(max), @PositionIndex int
SET @EmailAddress = ‘ jchinchilla @ sqljoe.com ‘ — (notice leading and trailing spaces and spaces before & after the @ sign)
SET @SpacePositions = ”
SET @PositionIndex = CHARINDEX(char(32),@EmailAddress)
WHILE @PositionIndex > 0
BEGIN
SET @SpacePositions =
CASE
    WHEN CHARINDEX(char(32),@EmailAddress,@PositionIndex+1) > 0
THEN @SpacePositions + CONVERT(varchar,@PositionIndex) + ‘, ‘
ELSE @SpacePositions + CONVERT(varchar,@PositionIndex)
END
SET @PositionIndex = CHARINDEX(char(32),@EmailAddress,@PositionIndex+1)
END
SELECT @SpacePositions as  SpacePositions
–Result=> 1, 13, 15, 26

Using REPLACE to find and remove all spaces in a string

REPLACE will allow you to substitute all spaces in a string with an empty string, thus removing all spaces in between characters. In the following example, all spaces represented by char(32) will be replaced with an empty string ”.

DECLARE @EmailAddress varchar(max)
SET @EmailAddress = ‘ jchinchilla @ sqljoe.com ‘ — (notice leading and traling spaces and spaces before and after the @ sign)
SELECT REPLACE(@EmailAddress, CHAR(32),”)
–Result=> jchinchilla@sqljoe.com –(all spaces have been removed)

Using REPLICATE to insert ‘n’ number of spaces in a string

SELECT  ‘A’ + REPLICATE(‘ ‘,5)  + ‘Z’
Result=> A_____Z  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show)
SELECT  ‘A’ + REPLICATE(char(32),5)  + ‘Z’
Result=> A_____Z  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show)
About these ads

3 Responses

  1. […] strings you will need to identify, insert or remove spaces before, after or in between characters. Jose Chinchilla blogs about the Identifying, inserting and removing spaces in strings regarding […]

  2. Hi! I’ve been following your weblog for some time now and finally got the courage to go ahead and give you a shout out from Atascocita Tx! Just wanted to tell you keep up the excellent job!

  3. Interesujące spojrzenie na rzecz, każdy powinien rozczytać i zaznajomić się
    z tematem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,639 other followers

%d bloggers like this: