RTRIM troubles - 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)

RTRIM troubles

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 04:31:06

Hi

I use this query...

SELECT ModelName
FROM dbo.Blad1
WHERE (RTRIM(ModelName) LIKE 'Volvo%')

And that give me one result (volvo of course), but if I replace the "LIKE 'Volvo%'" to " = 'Volvo'", I dont get any result at all.

The modelname is a nVarchar (50) column.

What am I doing wrong?

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 04:36:39

[code]DECLARE @Sample TABLE (RowID INT IDENTITY, ModelName NVARCHAR(50))

INSERT @Sample
SELECT 'Volvo 750' UNION ALL
SELECT 'Volvo ' UNION ALL
SELECT 'Volvo'

SELECT * FROM @Sample

SELECT *
FROM @Sample
WHERE RTRIM(ModelName) LIKE 'Volvo%' -- no need for RTRIM

SELECT *
FROM @Sample
WHERE ModelName LIKE 'Volvo%'

SELECT *
FROM @Sample
WHERE RTRIM(ModelName) LIKE 'Volvo' -- need for rtrim

SELECT *
FROM @Sample
WHERE ModelName LIKE 'Volvo'[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 04:49:22

Hi Peso

I tryed your example and it worked just fine.

But it doesnt work when I try my table..

This work..

WHERE (RTRIM(ModelName) LIKE 'Volvo%')

But this does not..

SELECT ModelName
FROM dbo.Blad1
WHERE (RTRIM(ModelName) = 'Volvo')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 04:52:15

Can you post FULL ddl for table (with COLLATION specification)?

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 05:06:43

I'm sorry but what do you mean?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 05:11:06

[code]CREATE TABLE [dbo].[test](
[a] [varchar](4) COLLATE Finnish_Swedish_CI_AS NULL,
[d1] [datetime] NULL,
[d2] [datetime] NULL,
[peso] [tinyint] NULL,
[x] [nvarchar](50) COLLATE Finnish_Swedish_CS_AI NULL
)[/code]This is one of my test tables. You have to post the DDL for your table.

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 05:18:22

Is this enough?

ID Int IDENTITY NOT NULL
ModelID Int NULL
ModelName nvarchar (50)
CodeListID int NOT NULL

Finnish_Swedish_CI_AS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 05:59:00

Still get same result

DECLARE	@Sample TABLE (RowID INT IDENTITY, ModelName NVARCHAR(50) COLLATE Finnish_Swedish_CI_AS)

INSERT	@Sample
SELECT	'Volvo 750' UNION ALL
SELECT	'Volvo   ' UNION ALL
SELECT	'Volvo'

SELECT	*
FROM	@Sample
WHERE	RTRIM(ModelName) LIKE 'Volvo%' -- no need for RTRIM

SELECT	*
FROM	@Sample
WHERE	ModelName LIKE 'Volvo%'

SELECT	*
FROM	@Sample
WHERE	RTRIM(ModelName) LIKE 'Volvo' -- need for rtrim

SELECT	*
FROM	@Sample
WHERE	ModelName LIKE 'Volvo'

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 06:20:14

Weird....

This should work for me to then...

SELECT *
FROM @Sample
WHERE RTRIM(ModelName) LIKE 'Volvo' -- need for rtrim

But it does not....

SELECT ModelName
FROM dbo.Blad1
WHERE (RTRIM(ModelName) LIKE 'Volvo')

no result at all...

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 06:22:48

When I mark the Word "Volvo" in the database I can see that there is several spaces after the word....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 06:42:24

Yes, just in my example above.

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

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 06:49:56

What don't you update your table to remove the trailing spaces? (Obviously you need to do this to any new data which is added in the future!)

UPDATE U
SET ModelName = RTRIM(ModelName)
FROM dbo.Blad1 AS U
WHERE ModelName LIKE '% ' -- Match values with trailing spaces

Then an EQUALS test will work (and probably be faster than using LIKE), plus you won't get trailing spaces / various values / etc. on reports, data exports, etc.

Kristen

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 06:58:11

Hi Kristen

When I try to run your code I get 0 rows affected..

Am I missing something?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 07:19:46

Maybe you don't have SPACES (ASCII 32)?
Maybe you have "hard spaces" (ASCII 160)?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 07:21:26

[code]DECLARE @Sample TABLE (RowID INT, ModelName NVARCHAR(50))

INSERT @Sample
SELECT 1, 'Volvo 750' UNION ALL
SELECT 2, 'Volvo ' UNION ALL
SELECT 3, 'Volvo' + CHAR(160) UNION ALL
SELECT 4, 'Volvo'

SELECT *, '_' + ModelName + '_'
FROM @Sample

SELECT *
FROM @Sample
WHERE RTRIM(ModelName) LIKE 'Volvo%' -- no need for RTRIM

SELECT *
FROM @Sample
WHERE ModelName LIKE 'Volvo%'

SELECT *
FROM @Sample
WHERE RTRIM(ModelName) LIKE 'Volvo' -- need for rtrim

SELECT *
FROM @Sample
WHERE ModelName LIKE 'Volvo'[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 07:22:03

Can I check what type it is?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 07:26:58

SELECT * FROM dbo.Blad1 WHERE ModelName LIKE '%' + CHAR(160) + '%'

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 07:31:47

This give no result..

SELECT ModelName
FROM dbo.Blad1
WHERE (RTRIM(ModelName) LIKE 'Volvo' + CHAR(160))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 08:15:43

SELECT ModelName
FROM dbo.Blad1
WHERE RTRIM(ModelName) LIKE 'Volvo%' + CHAR(160) + '%'

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 08:25:19

Hi

Yes that give me results, do you know how I can replace char(160)?

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-08-29 : 08:37:23

Hi

I did it this way....

UPDATE U
SET ModelName = REPLACE(ModelName, CHAR(160),'')
FROM dbo.Blad1 AS U

UPDATE U
SET ModelName = RTRIM(ModelName)
FROM dbo.Blad1 AS U
WHERE ModelName LIKE '% ' -- Match values with trailing spaces

And now it works fine.

Thanks for all help!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources