CODEWITHSUNDEEP
sqlsql-server
Mat
Mat

Reputation: 223

SQL Select Statement for text within a field that includes double and or single quotes

I am trying to do the following query in SQL Server Manager:

SELECT TOP 100 *
FROM [myDB].[dbo].[content]
where content_html like %<images><img src='/' alt=''></img></images>%'

I need to search for that exact string that exists in numerous content items and is breaking an XML feed I am creating. 

<images><img src='/' alt=''></img></images>

The problem here are the single quotes while performing the query.

Is there any way to do this?

Upvotes: 0

Views: 43

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

If you need to search for ' you need to double it ('' inside '...' is treated as single '):

CREATE TABLE #content(content_html VARCHAR(100));

INSERT INTO #content(content_html)
VALUES ('<images><img src=''/'' alt=''''></img></images>')

SELECT TOP 100 *
FROM #content
where content_html like '%<images>%<img src=''/'' alt=''''></img>%</images>%'

LiveDemo

Output:

<images><img src='/' alt=''></img></images>

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

You need to add wrap single quotes with single quotes. Try this

declare @var varchar(4000) = '<images><img src=''/'' alt=''''></img></images>'

SELECT TOP 100 *
FROM [myDB].[dbo].[content]
where content_html like '%'+var+'%'

Also using TOP without Order by does not guarantee same TOP 100 result everytime

Upvotes: 2

George Moralis
George Moralis

Reputation: 516

use escape characters

SELECT TOP 100 * FROM [myDB].[dbo].[content] where content_html like ''%%''

should work

Upvotes: 0

Related Questions