WHERE Field = @var problem - URGENT SOLUTION NEEDED - 1

[mlibeson]

my code looks something like:

DECLARE @var AS VARCHAR (20);

SET @var = 'A/B';

SELECT * FROM Tablename
WHERE LTRIM(RTRIM(Field)) = LTRIM(RTRIM(@var));

It does not return any rows.

It does return rows if I write the code as follows:

SELECT * FROM Tablename
WHERE LTRIM(RTRIM(Field)) = 'A/B';

Can anyone help me with this problem?


Michael Libeson

 

[bborissov]

What type is Field? Also there is no need to TRIM varchar field or variable it is already trimmed. What happens with:

DECLARE @var AS VARCHAR (20)
SET @var = 'A/B'

SELECT * FROM Tablename
       WHERE Field = @var

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

works for me

create table Tablename (Field varchar(20))
insert Tablename values ('A/B')

DECLARE @var AS VARCHAR (20);

SET @var = 'A/B';

SELECT * FROM Tablename
WHERE LTRIM(RTRIM(Field)) = LTRIM(RTRIM(@var));

why are you trimming anyway don't you know varchars get trimmed

SELECT * FROM Tablename
WHERE Field = @var;

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mlibeson]

I am aware that varchar does not require trim but I was having problems and was trying to eliminate scenarios.

The Field type is varchar as well.

Michael Libeson

 

[mlibeson]

Silly me.

In a rush to modify existing code to search based on ID and change the search to the description, I never changed the length of varchar so the string was being truncated.

Oh the shame for even posting this issue.

This one goes on the wall of shame.

Thanks for your help anyway.

I appreciate your quick response and efforts.


Michael Libeson

 

[gmmastros]

I suspect that there are 'non=printable' characters contained within the data. Take a look at this example (where the data has a Tab character at the end of it.

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('A/B' + Char(9))

Declare @var VarChar(20)

Set @Var = 'A/B'

Select 'With = compare' As CompareType, 
       * 
From   @Temp 
Where  Data = @Var

Select 'With Like compare' As CompareType,
       * 
From   @Temp 
Where  Data Like '%' + @var + '%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Since you say your code is SOMETHING like that, that could be the issue - we aren't working with the real data.

Try this with your real data:

DECLARE @var AS VARCHAR (20)
SET @var = 'A/B'

PRiNT 'This is the value trimmed ' + LTRIM(RTRIM(@var)) + '.'
PRINT 'This is the value untrimmed ' + @var + '.'

Are they the same? Notice that there should be one space between the end of the first string and the variable and no space after the variable and the period.

Also, using semi-colons after each line isn't common in SQL Server, but it is common in Oracle. You are using SQL Server, aren't you?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Looks like george and I were typing our responses when you found and posted your solution. Oh well, it's all in the timing.

I'm glad you solved the issue AND posted what happened.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

>>, using semi-colons after each line isn't common in SQL Server

it is required for some parts in SQL server 2005

http://sqlblogcasts.com/blogs/simon...ce-Broker---Dont-forget-your-semi-colons.aspx

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

SQLDenis,

Thanks for that bit of knowledge....I wasn't aware of that need as I'm mostly a SQL Server 2000 guy and just starting with 2005.

-SQLBill

Posting advice: FAQ481-4875

 

[mlibeson]

SQLDenis,

Thanks for the info. I have always used the semi-colon as I thought it was part of the standard SQL even though SQL Server 2000 and prior did not require it. It looks like it will be required going forward so I am glad I have already been using them in practice.


Michael Libeson