trim and update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trim and update

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-01-21 : 02:46:43

Hi

I have a need to iterate through a table and trim a certain column so that all spaces around a word is removed, can someone show me how to do this?

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:11:05

If the word is the only text in the column then there are easier ways. If you mean that the word is within other text then possibly this:


DECLARE @intRowCount int
SET @intRowCount = 1 -- Force first iteration
WHILE @intRowCount >0
BEGIN
    UPDATE U
    SET MyColumn = REPLACE(REPLACE(MyColumn, 'MyWord ', 'MyWord'), ' MyWord', 'MyWord')
    FROM MyTable AS U
    WHERE MyColumn LIKE '% MyWord%' OR MyColumn LIKE '%MyWord %'
    SELECT @intRowCount = @@ROWCOUNT
END

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-01-21 : 03:26:38

Hi

Thanks, but doesn't this code just replace a fixed word (MyWord)?
In my case the Mycolumn have values that varies.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-21 : 03:30:29

you want to remove spaces around a specific word ? or remove all spaces in the column ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:31:27

In that case I don't understand your problem accurately.

Can you explain it more fully please, with some Before / After examples.

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-01-21 : 03:59:17

Hi

Lets say that the column contain some words like this...

"Streetadress 5

"

In this case I dont want the extra linebreak after the adress, so I need to remove that so the final result look like this..

"Streetadress 5"

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 04:29:12

[code]
UPDATE U
SET MyColumn = RTrim(MyColumn),
MyColumn = CASE WHEN MyColumn LIKE '%' + CHAR(13) + CHAR(10)
THEN RTrim(LEFT(MyColumn, DATALENGTH(MyColumn-2)))
ELSE MyColumn
END
FROM MyTable AS U
WHERE RTRIM(MyColumn) LIKE '%' + CHAR(13) + CHAR(10)
OR MyColumn LIKE '% '
[/code]
You'll need to repeat that (e.g. using my "loop" code above) in case there are multiple CR/LF on the end of the column.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-21 : 05:21:34

If ur front end would be .net based app.just do it in front end itself just using a built in Function(Trim())available.

ex below

value.Trim();

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 05:28:01

.net TRIM() removes CR + LF? I think that is unlikely ....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 03:06:34

quote:
Originally posted by Kristen

.net TRIM() removes CR + LF? I think that is unlikely ....

Is it? I dont think so

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 03:58:36

I looked in the .Net docs and didn't find anything about TRIM remocing CR + LF, but maybe I missed something?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 04:34:26

quote:
Originally posted by Kristen

I looked in the .Net docs and didn't find anything about TRIM remocing CR + LF, but maybe I missed something?

I misunderstand that you were telling that .NET Trim would remove CR+LF. That was why my previous reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources