-2

I'm trying to convert this '232431 K' into this '232431K' and I can't find a function that does this. All the references I have found are TRIM, RTRIM and LTRIM but these functions are not useful for me in this case because I don't have left or right white spaces to be removed, the white spaces are "inside" the string.

Miquel
  • 442
  • 3
  • 14

3 Answers3

3

You can refer to Remove all spaces from a string in SQL Server.

In simple terms, you will need to use REPLACE function, such as REPLACE(stringValue, ' ', '')

Abishek
  • 152
  • 1
  • 7
  • I see, thanks. What if I have to replace ' ' or '-', is it possible to put it all using just one REPLACE function? For example REPLACE(stringValue, [' ', '-'], '') – Miquel Feb 23 '22 at 10:50
1

Maybe try REPLACE like below

SELECT  REPLACE(N'232431 K',' ','')

As asked in another comment

What if I have to replace ' ' or '-', is it possible to put it all using just one REPLACE function? For example REPLACE(stringValue, [' ', '-'], '')

you should use replace in conjuction with Translate function

select replace(translate (N'l;[pi-',';[-','   '),' ','')
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
1

In addition to using replace, on SQL Server 2017+ to avoid multiple nested functions, you can use translate if there are multiple characters in a string you want to remove:

Remove space, hyphen, slash:

declare @string varchar(50)='12345 67-9\x'

select Replace(Translate(@string, ' -\','???'),'?','')
Stu
  • 30,392
  • 6
  • 14
  • 33