UPDATE using TRIM - 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

General SQL Server Forums

New to SQL Server Programming

UPDATE using TRIM

Author

Topic

david_reinjal
Starting Member

36 Posts

Posted - 2007-01-03 : 06:54:44

hi guys,

i need to update a column by trimming the right end. i tried many things but its not working. can anyone tell me?

Regards,
David

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 06:59:15

Have you tried RTRIM()? If it is not working, I guess you are having non-printing characters in your data like char(160).

In that case, you can use following query to trim it:

Update tbl
set col = replace(col, char(160), '')

But unless you know exactly what non-printing characters are embedded in the data, it is of little use.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-03 : 09:25:49

quote:
Originally posted by david_reinjal

hi guys,

i need to update a column by trimming the right end. i tried many things but its not working. can anyone tell me?

Regards,
David

Can you post the query you used?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 11:08:06

Using Harsh's suggestion to take care of both trailing soft-spaces and hard-spaces.

Update tbl
set col = replace(rtrim(replace(col, char(160), ' ')), ' ', char(160))

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 11:44:50

Nice trick Peter. I didn't consider the space characters inside the string!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 07:50:51

What are soft and hard?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 08:45:19

When using soft spaces, char(32), web applications and Word linebreak the word.
Say you have the number "15 000 000". If that number is to near the right edge of the document, Word or web page linebreaks the word in either

15
000 000

or

15 000
000

But if you use hard spaces, char(160), Word or web page does not line break the number.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 08:52:19

"set col = replace(rtrim(replace(col, char(160), ' ')), ' ', char(160))"

That's gonna have the side effect of making all soft-space hard ...

... which leaves using some other-wise-unused intermediate character ...

I like the idea though Peso - e.g. to get rid of leading zeros, but leave any other embedded zeros ...

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 09:56:34

Good point!

SET col = replace(rtrim(replace(col, char(160), char(7))), char(7), char(160))

char(7) is th ansi code for BELL... In the old MSDOS programming days, ussed for signal or BEEP.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 10:08:50

Thanks Peso

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 11:11:45

I don't think we are there yet ...

I suggest:

RTrim leading space
Change SPACE to CHAR(7)
Change CHAR(160) to space
RTrim leading space again
Change space to CHAR(160)
Change CHAR(7) to SPACE

That will miss any combination Space / CHAR(160) leading characters. But it will trim either/or.

What a palaver!

I wonder if there is an easier way?

Could you join the value to a Tally table to work out the lowest number position that is either SPACE or CHAR(160) and then use SUBSTRING or RIGHT from that offset?

Can't visualise whether that would be efficient, or not ...

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 11:27:33

declare @s varchar(10)

select @s = 'Peso' + char(160) + char(32) + char(160)

select @s,
'_' + left(@s, datalength(@s) - patindex('%[^ ' + char(160) + ']%', reverse(@s)) + 1) + '_'

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 11:56:05

Yeah, that looks more like it!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources