Solved

How to use LIKE keyword on numeric datatype in a SQL query.

Posted on 2006-07-24
3
227 Views
Last Modified: 2010-04-23
Hi all,
   I have build a generic user control which perform a customized search on data. I am using a datatable namly oDataTable passed to the control. And then I use the select method of oDatatable to search a column data character by character or digit by digit. I have used like keyword in query which is suitable for this situation. It works well when column datatype is char, or varchar. However, when this search performed on a column has numeric datatype then it generates error stating "like cannot be used with Decimal or string datatype".
The code is as follow:

Dim oDataRow as DataRow
oDataRow = oDataTable.Select(ColumnName & " like '" & txtSearch.Text & "%'", ColumnName)

Note: the ColumnName is the Column Name on which I want to perform search and has type numeric.
Note also that if u r going to give me solution as to use ">="  like:
oDataRow = oDataTable.Select(ColumnName & " >= " & txtSearch.Text, ColumnName)

then this will not work because I am going partial searching. This returns all the numbers from a number to be searched. For it returns 9, 10,... for digit 8.
In a nutshell, when I perform search on digit 8, the result should return either 8, if it is not found then 80 or 81, ..., or 89, if these also not found then 800 or 801, ... 899, and so on.

0
Comment
Question by:AfzalHaidry
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 100 total points
ID: 17169592
try usinf CAST:

WHERE CAST(ColumnName AS nvarchar) LIKE '" & txtSearch.Text & "%'"
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17169601
or

WHERE CAST(ColumnName AS varchar(12)) LIKE '" & txtSearch.Text & "%'"
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

18 Experts available now in Live!

Get 1:1 Help Now