[dba-SQLServer] SQL Server LIKE and non-string data types

Thanks for replying -- so the conversion is implicit then?

Here's a link to a microsoft article:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Thumb down about 3/4's of the way and you'll see some examples using CAST 
with LIKE -- reading this I assumed that the following wouldn't work, but it 
did:

USE AdventureWorks
SELECT ProductID, Weight / 454 As Poundage
FROM Production.Products
WHERE WeightUnitMeasureCode = 'G' AND Weight / 454 = '1%'

I don't know whether the documentation is misleading/incorrect or I'm 
misunderstanding the example. So far, I've not been able to generate a LIKE 
that doesn't work without an explicit conversion.

Susan H.


----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Discussion concerning MS SQL Server" 
<dba-sqlserver at databaseadvisors.com>
Sent: Sunday, January 20, 2008 11:47 PM
Subject: Re: [dba-SQLServer] SQL Server LIKE and non-string data types


What documentation are you referring to?

>From SQL Server 2000 BOL:

LIKE
.......
"If any of the arguments are not of character string data type, Microsoft® 
SQL
ServerTM converts them to character string data type, if possible."

(Don't have v7 documentation available here, but I'm pretty sure it was the 
same back then.)

Cheers,
Stuart

On 20 Jan 2008 at 14:06, Susan Harkins wrote:

> I expected the following statement to fail
>
> USE AdventureWorks
> SELECT ProductID, Weight
> FROM Production.Product
> WHERE Weight LIKE '4%'
>
> But it works. The documentation implies that LIKE won't work with 
> non-string
> data types and that you must use CAST to convert them first -- but this
> works as expected without CAST. Weight is a decimal data type. I'm using 
> SQL
> Server Express (2005) so I'm wondering if this is a version thing -- maybe
> 2005 forces an implicit conversion?
>
> Susan Harkins
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com