CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

How to make LIKE in SQL look for specific string instead of just a wildcard

My SQL Query:

SELECT
    [content_id] AS [LinkID]
    , dbo.usp_ClearHTMLTags(CONVERT(nvarchar(600), CAST([content_html] AS XML).query('root/Physicians/name'))) AS [Physician Name]
FROM
    [DB].[dbo].[table1]
WHERE
    [id] = '188'
    AND
    (content LIKE '%Urology%')
    AND
    (contentS = 'A')
ORDER BY
    --[content_title]
    dbo.usp_ClearHTMLTags(CONVERT(nvarchar(600), CAST([content_html] AS XML).query('root/Physicians/name')))

The issue I am having is, if the content is Neurology or Urology it appears in the result.

Is there any way to make it so that if it's Urology, it will only give Urology result and if it's Neurology, it will only give Neurology result.

It can be Urology, Neurology, Internal Medicine, etc. etc... So the two above used are what is causing the issue.

The content is a ntext column with XML tag inside, for example:

<root><Location><location>Office</location>
<office>Office</office>
<Address><image><img  src="Rd.jpg?n=7513" /></image>
<Address1>1 Road</Address1>
<Address2></Address2>
<City>Qns</City>
<State>NY</State>
<zip>14404</zip>
<phone>324-324-2342</phone>
<fax></fax>
<general></general>
<from_north></from_north>
<from_south></from_south>
<from_west></from_west>
<from_east></from_east>
<from_connecticut></from_connecticut>
<public_trans></public_trans>
</Address>
</Location>
</root>

With the update this content column has the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <Physicians>
        <name>Doctor #1</name>
        <picture>
            <img src="phys_lab coat_gradation2.jpg?n=7529" />
        </picture>
        <gender>M</gender>
        <langF1>
            <a href="/ult.aspx" title="English">English</a>
        </langF1>
        <specialty>
            <a title="Neurology" href="neu.aspx">Neurology</a>
        </specialty>
    </Physicians>
</root>

If I search for Lab the result appears because there is the text lab in the column.

Upvotes: 0

Views: 127

Answers (4)

asontu
asontu

Reputation: 4659

This is what I would do if you're not into making a CLR proc to use Regexes (SQL Server doesn't have regex capabilities natively)

SELECT
    [...]
WHERE
    (content LIKE @strService OR
     content LIKE '%[^a-z]' + @strService + '[^a-z]%' OR
     content LIKE @strService + '[^a-z]%' OR
     content LIKE '%[^a-z]' + @strService)

This way you check to see if content is equal to @strService OR if the word exists somewhere within content with non-letters around it OR if it's at the very beginning or very end of content with a non-letter either following or preceding respectively.

[^...] means "a character that is none of these". If there are other characters you don't want to accept before or after the search query, put them in every 4 of the square brackets (after the ^!). For instance [^a-zA-Z_].

Upvotes: 2

John Go-Soco
John Go-Soco

Reputation: 955

Judging from your content, can you not leverage the fact that there are quotes in the string you're searching for?

SELECT [...] WHERE (content LIKE '%""Urology""%')

Upvotes: 0

Aaron Digulla
Aaron Digulla

Reputation: 328604

Databases are notoriously bad at semantics (i.e. they don't understand the concept of neurology or urology - everything is just a string of characters).

The best solution would be to create a table which defines the terms (two columns, PK and the name of the term).

The query is then a join:

join table1.term_id = terms.term_id and terms.term = 'Urology'

That way, you can avoid the LIKE and search for specific results.

If you can't do this, then SQL is probably the wrong tool. Use LIKE to get a set of results which match and then, in an imperative programming language, clean those results from unwanted ones.

Upvotes: 1

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

As I see it, your options are to either:

  1. Create a function that processes a string and finds a whole match inside it
  2. Create a CLR extension that allows you to call .NET code and leverage the REGEX capabilities of .NET

Aaron's suggestion is a good one IF you can know up front all the terms that could be used for searching. The problem I could see is if someone searches for a specific word combination.

Upvotes: 1

Related Questions