Some Useful String Functions in SQL

Let’s try to have a look at some SQL functions that are not very popular but can be very useful in some cases.

Let’s imagine that we have the following task: replace all letters in a varchar column between first and last spaces with symbol ‘*’. The task can be solved with CTE, but we try to do it using only string function.

So, let’s try.

We’ve got a variable:

Declare @stPar varchar(256) = ‘Journey to the other side of the Moon.’

The required result must looks like ‘Journey ************************ Moon.’

It’s very easy to get the first position of space in the string:

— Find the first space in the string
Declare @FirstSpace int
select @FirstSpace = charindex(‘ ‘, @stPar)

Getting position of the last space can be a nightmare, except we use the function that returns the reverse order of a string value:

— Find the last space in the string
Declare @LastSpace int
select @LastSpace = charindex(‘ ‘, reverse(@stPar))

So all preparations are finished, and let’s try three different ways of solving the task.

In the first solution I put the needed amount of spaces between first part and second part of the string and then replace then with ‘*’:

— Replace all letters between first space and last space with * – use Space
select substring(@stPar, 1, @FirstSpace) + replace(space(datalength(@stPar) – @LastSpace – @FirstSpace), ‘ ‘, ‘*’) + substring(@stPar, datalength(@stPar) – @LastSpace + 1, @LastSpace)

Some explanations:

  • substring(@stPar, 1, @FirstSpace) – get part of the string till the first space
  • replace(space(datalength(@stPar) – @LastSpace – @FirstSpace), ‘ ‘, ‘*’) – first we create string with needed amount of spaces and then we replace all of them with ‘*’ symbol. We use datalength, not len function, because if we have string that consists of all spaces, len function will return 0, and calculation will fail.
  • substring(@stPar, datalength(@stPar) – @LastSpace + 1, @LastSpace) – get part of the string from the last space till the end.

Now, let’s do the same operation using the function that Repeats a string value a specified number of times:

select substring(@stPar, 1, @FirstSpace) + replicate(‘*’, (datalength(@stPar) – @LastSpace – @FirstSpace)) + substring(@stPar, datalength(@stPar) – @LastSpace + 1, @LastSpace)

Explaining new code:

  • replicate(‘*’, (datalength(@stPar) – @LastSpace – @FirstSpace)) – we get the string where ‘*’ repeats the required number of time.

And the last case – using STUFF function:

SELECT stuff(@stPar, @FirstSpace + 1, datalength(@stPar) – @LastSpace – @FirstSpace, replicate(‘*’, datalength(@stPar) – @LastSpace – @FirstSpace))

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

So, now let’s get the complete solution of the task.

declare @StringValues as table
       StringColumn varchar(256)
insert into @StringValues (StringColumn) values (‘Journey to the other side of the Moon.’)
insert into @StringValues (StringColumn) values (‘                               ‘)
insert into @StringValues (StringColumn) values (‘Some string’)
insert into @StringValues (StringColumn) values (‘Some string value ‘)
             TabMain.FirstSpace + 1,
             replicate(‘*’, TabMain.LenToReplace)
       ) ResultString,
       TabMain.StringColumn OriginalString
from (
             charindex(‘ ‘, sv.StringColumn) FirstSpace,
             datalength(sv.StringColumn) – charindex(‘ ‘, reverse(sv.StringColumn)) – charindex(‘ ‘, sv.StringColumn) LenToReplace,
       from @StringValues sv
       where sv.StringColumn like ‘% % %’ — to remove lines with single space
) TabMain

And the result will be the following:


This entry was posted in Database development, SQL Server, Technology and tagged , , , . Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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