about "trailing Space" in the record

Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.

How can I make SQL to return different result?

Thanks.

That is the result of the ANSI standard.

One way that you could use to determine if the values, including trailing blanks, are different, is to use the datalength() functions. Something like this:

Code Snippet

DECLARE
@.MyVar1 varchar(20),
@.MyVar2 varchar(20)

SELECT
@.MyVar1 = 'data',
@.MyVar2 = 'data '

IF datalength( @.MyVar1 ) = datalength( @.MyVar2 )
PRINT 'Values are the same'
ELSE
PRINT 'Values are NOT the same'

|||Thanks Arnie.

My case is a bit different. I need to run a sql statement (DELETE FROM MYTABLE WHERE key='KEYVALUE') from an application. The data in the table is imported from raining data. Some data has trailing space. Because of the ANSI standard, 'KEYVALUE ' will be deleted if I run that statement which is not I want.

I just wonder if there is a switch to we can turn it on/off to make it different.|||

Perhaps this will work for you:

WHERE ( KeyValue = 'KeyValue'

AND datalength( KeyValue ) = datalength( 'KeyValue' )
)

|||Thanks. That will do.

a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

-------------------

A performance question: "SELECT ID as subID FROM myTable AS myTable1"

For some reasons I need to access the same field of the same table twice in a query, and each give out a diferrent value
Like this:
"SELECT myTable.id, myTable1.id as subID FROM myTable INNER JOIN ... INNER JOIN myTable as myTable1 ..."
The question is, when I write it as myTable as myTable1 will it affect the query performance if myTable is a large table? will it create another so big copy of myTable? or I should create a view like "CREATE VIEW myTable1 AS SELECT id FROM myTable" to reduce the side of myTable1?
Thank you.

Views are actually slower than stored procedures...