+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Remove all spaces from a string in SQL Server (/Thread-Remove-all-spaces-from-a-string-in-SQL-Server)
Remove all spaces from a string in SQL Server - plaything245 - 07-31-2023
What is the best way to remove all spaces from a string in SQL Server 2008?
`LTRIM(RTRIM(' a b '))` would remove all spaces at the right and left of the string, but I also need to remove the space in the middle.
RE: Remove all spaces from a string in SQL Server - maeganfmzsjkve - 07-31-2023
I would use a REPLACE
select REPLACE (' Hello , How Are You ?', ' ', '' )
[REPLACE][1]
[1]:
[To see links please register here]
RE: Remove all spaces from a string in SQL Server - Sircanadian0 - 07-31-2023
t-sql replace
[To see links please register here]
replace(val, ' ', '')
RE: Remove all spaces from a string in SQL Server - eulaheulalee230 - 07-31-2023
[`REPLACE()` function](
[To see links please register here]
):REPLACE(field, ' ', '')
RE: Remove all spaces from a string in SQL Server - Proethelinetnshslais - 07-31-2023
If there are multiple white spaces in a string, then replace may not work correctly. For that, the following function should be used.
CREATE FUNCTION RemoveAllSpaces
(
@InputStr varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @InputStr
while charindex(' ', @ResultStr) > 0
set @ResultStr = replace(@InputStr, ' ', '')
return @ResultStr
END
Example:
select dbo.RemoveAllSpaces('aa aaa aa aa a')
Output:
aaaaaaaaaa
RE: Remove all spaces from a string in SQL Server - mousebirds345199 - 07-31-2023
If it is an update on a table all you have to do is run this update multiple times until it is affecting 0 rows.
update tableName
set colName = REPLACE(LTRIM(RTRIM(colName)), ' ', ' ')
where colName like '% %'
RE: Remove all spaces from a string in SQL Server - curnin564 - 07-31-2023
Just in case you need to TRIM spaces in all columns, you could use this script to do it dynamically:
--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'
--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable
declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '
--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '
WHILE @i <= @tri
BEGIN
IF (@i = @tri)
BEGIN
set @comma = ''
END
SELECT @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
FROM #tempcols
where id = @i
select @i = @i+1
END
--execute the entire query
EXEC sp_executesql @trimmer
drop table #tempcols
RE: Remove all spaces from a string in SQL Server - loggias873058 - 07-31-2023
if you want to remove spaces,-, and another text from string then use following :
suppose you have a mobile number in your Table like '718-378-4957' or
' 7183784957' and you want replace and get the mobile number then use following Text.
select replace(replace(replace(replace(MobileNo,'-',''),'(',''),')',''),' ','') from EmployeeContactNumber
Result :-- 7183784957
RE: Remove all spaces from a string in SQL Server - arabellavpkgosxf - 07-31-2023
To remove the spaces in a string left and right. To remove space in middle use `Replace`.
You can use `RTRIM()` to remove spaces from the right and `LTRIM()` to remove spaces from the left hence left and right spaces removed as follows:
SELECT * FROM table WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("Bob alias baby"))
RE: Remove all spaces from a string in SQL Server - Trix785413 - 07-31-2023
**[Reference taken from this blog:][1]**
[1]:
[To see links please register here]
**First, Create sample table and data:**
CREATE TABLE tbl_RemoveExtraSpaces
(
Rno INT
,Name VARCHAR(100)
)
GO
INSERT INTO tbl_RemoveExtraSpaces VALUES (1,'I am Anvesh Patel')
INSERT INTO tbl_RemoveExtraSpaces VALUES (2,'Database Research and Development ')
INSERT INTO tbl_RemoveExtraSpaces VALUES (3,'Database Administrator ')
INSERT INTO tbl_RemoveExtraSpaces VALUES (4,'Learning BIGDATA and NOSQL ')
GO
**Script to SELECT string without Extra Spaces:**
SELECT
[Rno]
,[Name] AS StringWithSpace
,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) AS StringWithoutSpace
FROM tbl_RemoveExtraSpaces
**Result:**
Rno StringWithSpace StringWithoutSpace
----------- ----------------------------------------- ---------------------------------------------
1 I am Anvesh Patel I am Anvesh Patel
2 Database Research and Development Database Research and Development
3 Database Administrator Database Administrator
4 Learning BIGDATA and NOSQL Learning BIGDATA and NOSQL