Search exact value on text field.

Posted on 2006-06-07
Last Modified: 2009-12-16
I have a table with a text field that was once used for notes.  Since then, they started updating all the entries in the table and they used the text field to mark the update status.

They used different codes in the text field to indicate the update that was made.

For example:

If it didnt need updating they used 'X'.
If partially updated, they used '*'
If fully updated, they used '**'
If they didn't know yet if it needed updating, they used '?'

Now they want a report based on the update status.

Of course we all know if I use:

SELECT * FROM table where textfield LIKE '%*%'

It will result in anything with *.  This is not good since I don't want ** records.  I just want * records.

How can I make it such that it searches the text field and matches exactly?  Not a wildcard method.


Question by:tsniff
    LVL 4

    Expert Comment

    The following query will give you the statistics of each text field,

    Select textfield , Count(*)
    From table
    Group By textfield

    Otherwise, you can do a select without using like and %

    SELECT * FROM table where textfield = '*'

    LVL 75

    Accepted Solution

    Depending on the contents you should at the very least be able to use PATINDEX with a wildcard search.

    Author Comment

    You cannot use = directly on text fields.  You will always end up with an error such as:

    Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    urTable WHERE CONTAINS (textField,'*')
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    In order to use this feature, full textindexing must be enabled...
    LVL 50

    Assisted Solution

    SELECT * FROM table where convert(char(3),textfield) = '*'


    Author Comment

    Both the patindex and convert worked.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now