How to remove spaces leaving only one?

Asked

Viewed 2,140 times

6

Considering a variable with a text in SQL Server. How can I remove 2 or more spaces within the text? In this case leaving only 1 space.

For example:

'1 tigre, 2  tigres, 3   tigres'

It should result in:

'1 tigre, 2 tigres, 3 tigres'
sql database sql-server

  • You want to leave only 1 space or none?

  • @Marconi only 1 even

1 answer

9


Do so:

declare @texto varchar(MAX)
set @texto = '1 tigre, 2  tigres, 3   tigres'

set @texto = replace(replace(replace(@texto,' ','<>'),'><',''),'<>',' ')
select @texto

Exit:

1 tiger, 2 tigers, 3 tigers

Explanation:

  • Trade all spaces for <>. replace(@texto,' ','<>'), will result in: 1<>tigre,<>2<><>tigres,<>3<><><>tigres
  • Swap all ><for ''. replace('1<>tigre,<>2<><>tigres,<>3<><><>tigres','><',''), will result in: 1<>tiger,<>2<>tigers,<>3<>tigers
  • Swap all <> by spaces(' '). replace('1<>tigre,<>2<>tigres,<>3<>tigres','<>',' '), will result in: 1 tiger, 2 tigers, 3 tigers

Sqlfiddle

Note: Replace works in virtually all languages or almost all. So this solution can be applicable to many cases.

Update

With the encouragement of @Jefferson Quesado and Udemy’s Course on Regexp added another example using Regex.

  1. Use the regular expression /[]{2,}/ to search for two or more occurrences of blank spaces.
  2. Utilize Replace and replace with nothing.

Query:

declare @texto varchar(MAX)
set @texto = '1 tigre, 2  tigres, 3       tigres'

set @texto = replace(@texto,'','/[]{2,}/')
select @texto

Sqlfiddle

  • 1

    This method is interesting because it works in virtually any language

  • 1

    @Sorack yes since replace is found in almost all languages. Good that I could help :)

  • 1

    @Marconi, do you know of regular expression in TSQL to solve this? I believe that if you have it you can solve it in a more satisfactory way, cleaner to read, at least

  • 1

    @Jeffersonquesado is a subject yet I have to study, but I tried to leave as detailed as possible the answer, putting as each replace will as a result. :)

  • 2

    It may seem like a sledgehammer to some, but in my opinion it’s a simple and effective way to solve the problem!

  • From what I understood of the first solution if the original string contains >< they will be lost at the end.

Show 1 more comment

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.