TRIM funtion - 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 2000 Forums

Transact-SQL (2000)

TRIM funtion

Author

Topic

ArielR
Starting Member

21 Posts

Posted - 2007-09-27 : 09:59:35

I need remove the right '0' from a field contents to perform a select, SQL Server 2000.

( mySQL has TRIM(LEADING, , ) function )

21010000000
22020200000
23020300000
24030300000

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:01:20

So you expected results are:

2101
220202
230203
240303

??
Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:03:00

SELECT SUBSTRING(Col1, 1, 10)
SELECT LEFT(Col1, 10)
SELECT STUFF(Col1, 11, 1, '')

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:09:33

Actually wouldn't PATINDEX on a REVERSE to find the last (i.e. first in the reverse version) non-zero character allow a LEFT or SUBSTRING of the remainder? (and avoid a LOOP)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 10:10:06

[code]
DECLARE @Sample TABLE (Data VARCHAR(200))

INSERT @Sample
SELECT '21010000000' UNION ALL
SELECT '22020200000' UNION ALL
SELECT '23020300000' UNION ALL
SELECT '24030300000'

SELECT Data,
LEFT(Data, 1 + LEN(Data) - PATINDEX('%[^0]%', REVERSE(Data)))
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:12:35

See? there you go. No sooner thought than Peso'd

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 12:49:26

New verb for the Oxford dictionairy: Peso'd

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:55:09

quote:

Originally posted by Peso


DECLARE	@Sample TABLE (Data VARCHAR(200))

INSERT	@Sample
SELECT	'21010000000' UNION ALL
SELECT	'22020200000' UNION ALL
SELECT	'23020300000' UNION ALL
SELECT	'24030300000'

SELECT	Data,
	LEFT(Data, 1 + LEN(Data) - PATINDEX('%[^0]%', REVERSE(Data)))
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

Or Double reverse with Single cast


SELECT	Data,
	reverse(cast(reverse(data) as bigint))
FROM	@Sample

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:57:26

quote:
Originally posted by DonAtWork

New verb for the Oxford dictionairy: Peso'd

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Yes. He introduces new words and phrases every week
I would call it as Kristenism

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 03:43:26

"I would call it as Kristenism"

Hahahaha ... now I've been OED'd too

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 05:01:54

quote:
I would call it as Kristenism

http://www.google.com.sg/search?q=Kristenism

Now we know who Kristen really is

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

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 06:00:08

OMG!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 06:22:29

I wonder how Kristenism is name of a person

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 06:27:02

Yeah, anything they said would be a Kristenismism ... Recurse!

ArielR
Starting Member

21 Posts

Posted - 2007-09-28 : 08:24:39

This code works fine!!.
Could you tell me if exist an equivalence in Access?

of:

SELECT Data,
LEFT(Data, 1 + LEN(Data) - PATINDEX('%[^0]%', REVERSE(Data)))
FROM @Sample

or:

SELECT Data,
reverse(cast(reverse(data) as bigint))
FROM @Sample

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 08:31:20

In ACCESS, my suggestion will work

SELECT Data,
reverse(cast(reverse(data) as bigint))
FROM table_name

Madhivanan

Failing to plan is Planning to fail

ArielR
Starting Member

21 Posts

Posted - 2007-09-28 : 08:44:26

I try it but have an error message: REVERSE function isn't defined.

ArielR
Starting Member

21 Posts

Posted - 2007-09-28 : 08:46:00

I try it but have an error message: REVERSE function isn't defined.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:17:53

Which version of Microsoft Access are you using?