• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Search exact value on text field.

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.


2 Solutions
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 = '*'

Anthony PerkinsCommented:
Depending on the contents you should at the very least be able to use PATINDEX with a wildcard search.
tsniffAuthor Commented:
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Aneesh RetnakaranDatabase AdministratorCommented:
urTable WHERE CONTAINS (textField,'*')
Aneesh RetnakaranDatabase AdministratorCommented:
In order to use this feature, full textindexing must be enabled...
SELECT * FROM table where convert(char(3),textfield) = '*'

tsniffAuthor Commented:
Both the patindex and convert worked.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now