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.
Asked
Active
Viewed 151 times
-2
-
1You're trying to replace characters in strings, not trim them. Use `REPLACE(somField,' ','')` – Panagiotis Kanavos Feb 23 '22 at 10:41
-
https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver15 – Arvo Feb 23 '22 at 10:42
3 Answers
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
- 16,585
- 6
- 37
- 57
1
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
- 132
- 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
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
- 19,456
- 3
- 10
- 28