CODEWITHSUNDEEP
sqlsql-server
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

How to look for a specific string using the LIKE command

I have the following WHERE statement in a SQL stored procedure that I am checking against a table which stores data as XML:

WHERE
(
    CAST ([content_html] AS XML).value('(/root/Physicians/specialty/a/text())[1]', 'varchar(max)') LIKE '%' + @strService + '%'
OR
    CAST ([content_html] AS XML).value('(/root/Physicians/specialty2/a/text())[1]', 'varchar(max)') LIKE '%' + @strService + '%'
OR
    CAST ([content_html] AS XML).value('(/root/Physicians/specialty3/a/text())[1]', 'varchar(max)') LIKE '%' + @strService + '%'
OR
    CAST ([content_html] AS XML).value('(/root/Physicians/specialty4/a/text())[1]', 'varchar(max)') LIKE '%' + @strService + '%'
OR
    CAST ([content_html] AS XML).value('(/root/Physicians/specialty5/a/text())[1]', 'varchar(max)') LIKE '%' + @strService + '%'
OR
    CAST ([content_html] AS XML).value('(/root/Physicians/specialty6/a/text())[1]', 'varchar(max)') LIKE '%' + @strService + '%'
)

strService is sent in from the front end to my code-behind which runs the stored procedure.

Each of the OR statement refers to each textbox in the XML document.

For example, If I send @strService = Urology the query searches for Urology, and Neurology, because Urology is also in Neurology

How can I 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.

Here is an example:

I have the following entries:

Pediatric Urology
Urology
Neurology

I have the following LIKE statement:

LIKE 'Urology' = only Urology is displayed

LIKE '% Urology = nothing is displayed

LIKE '%[^a-zA-Z]Urology' = only Pediatric Urology is displayed

I would like to show Pediatric Urology and Urology in this case.

Pretty much as long as the word exist with a space in front of it, I would like to show.

Upvotes: 0

Views: 95

Answers (2)

Phil Gabardo
Phil Gabardo

Reputation: 142

Use the following expression:

CONTENT LIKE @strService 
OR CONTENT LIKE '%[^a-zA-Z0-9]' + @strService + '[^a-zA-Z]%' 
OR CONTENT LIKE @strService + '[^a-zA-Z]%'
OR CONTENT LIKE '%[^a-zA-Z]' + @strService

Heres an explanation:

 CONTENT LIKE @strService

will check for equality

 CONTENT LIKE '%[^a-zA-Z0-9]' + @strService + '[^a-zA-Z]%'

will check for the case where the word exists somewhere within content with non-letters around it

 CONTENT LIKE @strService + '[^a-zA-Z]%'

will check for the case where the word exists at the very beginning of content with a non-letter following

 CONTENT LIKE '%[^a-zA-Z]' + @strService

will check for the case where the word exists at the very end of content with a non-letter preceding.

If you'd like to exclude numbers from the set of possible adjacent letters, use [^a-zA-Z0-9] in place of [^a-zA-Z].

Hope this helps!

Upvotes: 1

deejay
deejay

Reputation: 575

The concatenation of the string depends on the type of RDBMS you are using
For eg:

Oracle and Postgrasql uses "||"
While SQL-server uses "+"

And the use of correct wild character will help you to find what you are looking for.

Upvotes: 0

Related Questions