I need to remove spaces present either side of a particular character in a string

Question

I have a very large string value which contains data separated by '|'.

For example

Declare @string NVARCHAR(MAX)

Set @String = 'My Name|Address 1|Address 2|......'

In some string values I have something like

@String = My Name|Address 1 | Address 2|.......

I need to remove Spaces on either side of '|'. If there is only one Space, I use

Set @string = Replace(@string,' |','|')    
Set @string = Replace(@string,'| ','|')

What if I have more than One space and the count cannot be specific like

@String = My Name|Address 1   | Address 2|.......

does the method described int http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x not work for you? — Joe, Jan 12 '15 at 04:26
use split function to split string then in splitted value use LTRIM-RTRIM and again concatenate back.There is the safest solution. — KumarHarsh, Jan 12 '15 at 05:19

score 0 · Answer 1 · answered Jan 12 '15 at 05:18

0

Combining both replace will fix your problem. Try this

Declare @string varchar(500)

set @String = 'My Name|Address 1 | Address 2| Address 3 |.......'

select  Replace(Replace(@string,' |','|'),'| ','|')

answered Jan 12 '15 at 05:18

Pரதீப்

91,748
19
131
172

score 0 · Answer 2 · answered Jan 12 '15 at 05:45

Here is one solution:

declare @s varchar(8000);
set @s = 'test                  |    test|test'

declare @tmp varchar(8000);
declare @x int;
set @x = 4096

while @x > 0
    begin
        set @tmp = replace(replace(@s, replicate(' ', @x) + '|', '|'), '|' + replicate(' ', @x), '|')
        if (@tmp = @s) set @x = @x / 2
        set @s = @tmp
    end

print @s

The idea is to replace spaces until replacement no longer changes the string. You can simply keep replacing ' |' and '| ' with '|', but the above code tryies to be a bit smarter and replace bigger chunks (starting from 4096 spaces and dividing by 2 on each step).

score 0 · Answer 3 · answered Jan 12 '15 at 07:39

0

This query might help you.

Declare @string

Set @String = 'My Name|Address 1  |  Address 2 |......'

--Query to remove spaces before and after a specific character

SET @String=(SELECT replace(replace(replace(@String, '|','|'),' ',''),'/t',' '))

answered Jan 12 '15 at 07:39

Sravani Annepu

118
1
13

This apparently removed space between My and Name. I am looking for something which removes spaces near '|'. – Sandeep Kurapati Jan 12 '15 at 14:39

Sarath Subramanian · Accepted Answer · 2015-01-12T15:16:13.457

0

I have written the logic inside the query

DECLARE @String NVARCHAR(MAX) = 'My Name|Address 1        |Address 2  |'
        -- Since '|' inside the STUFF removes the first word, we append that word
SELECT  LEFT(LTRIM(@String),1) + STUFF((SELECT '|' + CAST(Ids AS VARCHAR(500)) [text()]
         FROM 
        (
            -- Converts each value to rows and remove the spaces on start and end
            SELECT LTRIM(RTRIM(PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(500)'),'-','.'),1))) 'Ids' 
            FROM  
            (
                 SELECT CAST ('<M>' + REPLACE(@String, '|', '</M><M>') + '</M>' AS XML) AS Data       
            ) AS A 
            CROSS APPLY Data.nodes ('/M') AS Split(a)
        )TAB
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,'') + '|' COLS

SQL FIDDLE

edited Jan 12 '15 at 15:16

answered Jan 12 '15 at 09:24

Sarath Subramanian

20,027
11
82
86

This will work without a loop and will have the best performance @Sandeep Kurapati – Sarath Subramanian Jan 12 '15 at 09:30
Yes this works, but it is removing the last '|' in the string. I am looking for something that can be used with loop. Thanks @Sarath Avanavu – Sandeep Kurapati Jan 12 '15 at 15:00
What if **|** is not in the string at end? Should I add **|** to the end anyway? @Sandeep Kurapati – Sarath Subramanian Jan 12 '15 at 15:12
We are using '|' to identify the columns from the table(Same as we have in text file). Here we are using '|' at the end to mark last column is ended without any extra blank spaces. There will be an '|' at the end to any string. – Sandeep Kurapati Jan 12 '15 at 15:15
I didn't get u. For what do u need a loop? @Sandeep Kurapati – Sarath Subramanian Jan 12 '15 at 15:33
Yes it will work. Is there any new requirement? @Sandeep Kurapati – Sarath Subramanian Jan 13 '15 at 01:47
Did u get the result as u expected? @Sandeep Kurapati – Sarath Subramanian Jan 15 '15 at 04:55

I need to remove spaces present either side of a particular character in a string

4 Answers4