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 ')
select
       stuff(
             TabMain.StringColumn,
             TabMain.FirstSpace + 1,
             TabMain.LenToReplace,
             replicate('*', TabMain.LenToReplace)
       ) ResultString,
       TabMain.StringColumn OriginalString
from (
       select
             charindex(' ', sv.StringColumn) FirstSpace,
             datalength(sv.StringColumn) - charindex(' ', reverse(sv.StringColumn)) - charindex(' ', sv.StringColumn) LenToReplace,
             StringColumn
       from @StringValues sv
       where sv.StringColumn like '% % %' -- to remove lines with single space
) TabMain

And the result will be the following:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *