Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
Course Of The Month
7 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sql Server Trimming blank spaces
Posted on 2014-11-27
Hi, I have a view which users connect via Excel.
Now I have a table used in the view where there is column named 'NAME' and it has data with spaces at the end
and in between like below :
Andrew - there is single space here right at the end of the word
John - there is double space here right at the end of the word
Gareth Jones - there is double space here in between Gareth and Jones
Thirlestane Endurance E-3120, 36m tower -- there are 3 spaces before the 36m tower
so when in my function, I need to trim these spaces in my select statement like below -
all spaces at the end of the word needs to be removed
all double and more than double spaces in between the texts should be replaced with a Single Space.
Can anyone please give me that code which I can use in my SELECT ?
Many Thanks
Now I have a table used in the view where there is column named 'NAME' and it has data with spaces at the end
and in between like below :
Andrew - there is single space here right at the end of the word
John - there is double space here right at the end of the word
Gareth Jones - there is double space here in between Gareth and Jones
Thirlestane Endurance E-3120, 36m tower -- there are 3 spaces before the 36m tower
so when in my function, I need to trim these spaces in my select statement like below -
all spaces at the end of the word needs to be removed
all double and more than double spaces in between the texts should be replaced with a Single Space.
Can anyone please give me that code which I can use in my SELECT ?
Many Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
2
10 Comments
Active today
Without functions there will always be a possible case which is not handled by a simple REPLACE() solution. This should handle the most cases:
DECLARE @Sample TABLE ( Payload NVARCHAR(255) );
INSERT INTO @Sample
VALUES ( 'Andrew ' ),
( 'John ' ),
( 'Gareth Jones' ),
( 'Thirlestane Endurance E-3120, 36m tower' ),
( ' Bob' ),
( 'Dr Who' );
DECLARE @S NVARCHAR(255) = ' ';
SELECT '[' + LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(Payload, @S + @S + @S + @S + @S + @S + @S + @S, @S), @S + @S + @S + @S, @S), @S + @S, @S), @S + @S, @S)))
+ ']'
FROM @Sample S;
Active 3 days ago
Hi Ste5an,
I don't want to display [ ] in my result. Can you do this without that ?
Thanks
I don't want to display [ ] in my result. Can you do this without that ?
Thanks
Active 3 days ago
Hi Haris,
Your syntax is incorrect..
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Your syntax is incorrect..
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Active 3 days ago
Hi Haris,
corrected you syntax error to select rtrim(replace(name, ' ','')) from mytable
but Gareth Jones is now becoming GarethJones which is incorrect...it should be Gareth Jones
corrected you syntax error to select rtrim(replace(name, ' ','')) from mytable
but Gareth Jones is now becoming GarethJones which is incorrect...it should be Gareth Jones
Active 3 days ago
Hi Ste5an,
I have now removed the [ ] from you code and got the result which I am expecting. can you please confirm if this will work in any space scenarios and just keep one space even if there are any number of spaces in between or the end ?
Thanks
I have now removed the [ ] from you code and got the result which I am expecting. can you please confirm if this will work in any space scenarios and just keep one space even if there are any number of spaces in between or the end ?
Thanks
Active today
Nope, it won't.
Only using a UDF to remove the spaces can to this. You need a split function (Arrays and Lists in SQL Server). Then split the value at each spaces. Then you recreate the value with single space.
Only using a UDF to remove the spaces can to this. You need a split function (Arrays and Lists in SQL Server). Then split the value at each spaces. Then you recreate the value with single space.
Featured Post
How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Long way back, we had to take help from third party tools in order to encrypt and decrypt data. Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Suggested Courses
Course of the Month7 days, 6 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.