Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

Sql Server Trimming blank spaces

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
0
gvamsimba
Asked:
gvamsimba
  • 7
  • 2
1 Solution
 
Haris DjulicCommented:
try like this :

select rtrim(replace(name, "  ","")) from table_name 

Open in new window

0
 
ste5anSenior DeveloperCommented:
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;

Open in new window

0
 
gvamsimbaAuthor Commented:
Hi Ste5an,

I don't want to display [ ] in my result. Can you do this without that ?

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gvamsimbaAuthor Commented:
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.
0
 
gvamsimbaAuthor Commented:
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
0
 
gvamsimbaAuthor Commented:
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
0
 
ste5anSenior DeveloperCommented:
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.
0
 
gvamsimbaAuthor Commented:
Hi ste5an,

is it possible for you to give that udf which can handle my scenario so that I can call that in my current udf ?

Many thanks
0
 
gvamsimbaAuthor Commented:
I achieved this by creating a function.
0
 
gvamsimbaAuthor Commented:
good
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now