Using LIKE in SQL to compare Money type field to a string

Posted on 2004-11-02
Last Modified: 2008-03-03

i have the following sql statement
" select bla bla bla...... where  Price  " like '%" & Request.form("selectvalue") & "%'"  

This sql statement works Ok in ACCESS but in SQL 2000 i get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

Any idea how to fix that ?
Question by:humer2000
    LVL 26

    Expert Comment


    " select bla bla bla...... where  CONVERT(VARCHAR(20), Price)  " like '%" & Request.form("selectvalue") & "%'"  
    LVL 26

    Accepted Solution

    or like this (shorter but does exactly the same)

    " select bla bla bla...... where  CAST(Price as VARCHAR)  " like '%" & Request.form("selectvalue") & "%'"  
    LVL 26

    Expert Comment

    the slight differences between the two syntaxes

    - <BOL>CAST is based on the SQL-92 standard and is preferred over CONVERT.</BOL>
    - Casting money datatype to varchar uses varchar(30) as default target datatype

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now