SQL query encountering issue after introducing apostrophe symbol ''`

Question

SQL query encountering issue after introducing apostrophe symbol ''`

SELECT CONCAT (
        schoolname
        ,CASE 
            WHEN schoolcity IS NULL
                OR schoolcity = ''
                THEN ''
            ELSE (', ' + REPLACE(schoolcity, 'ã', 'a'))
            END
        ,CASE 
            WHEN schoolname IS NULL
                OR schoolstate = ''
                THEN ''
            ELSE (', ' + schoolstate)
            END
        ) AS schoolname
FROM schools
WHERE schoolname LIKE '"+filter+"%'
ORDER BY schoolname

This particular query is causing some unexpected behavior during execution. It seems like when an apostrophe is added in the filter from the search bar, the desired results are not being displayed.

For example: Trying to search for "Types children'" and nothing appears

The expected outcome should be to display school names containing "children's". Can anyone provide assistance with this issue?

javascript asp.net .net sql-server

Answer 1

Answer №1

It seems that the issue lies with how your WHERE clause is being processed:

WHERE schoolname LIKE 'children'%'

When you input an apostrophe, it is mistakenly recognized as the end of the value. To include another apostrophe, it must be escaped as a double apostrophe.

If you type this into the search bar, it should be understood as:

WHERE schoolname LIKE 'children''%'

Test these query options in SSMS directly to observe the distinction.

Answer 2

It seems that the issue lies with how your WHERE clause is being processed:

WHERE schoolname LIKE 'children'%'

When you input an apostrophe, it is mistakenly recognized as the end of the value. To include another apostrophe, it must be escaped as a double apostrophe.

If you type this into the search bar, it should be understood as:

WHERE schoolname LIKE 'children''%'

Test these query options in SSMS directly to observe the distinction.

Answer 3

Answer №2

Remember to always use two quotes when using apostrophes like ''

Here is a straightforward query:

select schoolname from school where schoolname like 'children''%'

If you are passing @filter as a variable, you can do the following:

declare @filter nvarchar(20)

select CONCAT(schoolname, CASE WHEN schoolcity IS NULL OR schoolcity = '' THEN '' Else  (', ' + REPLACE(schoolcity,'ã','a')) END,CASE WHEN schoolname IS NULL OR schoolstate = '' THEN '' Else (', ' + schoolstate) END) as schoolname from schools where schoolname like '%' + @filter + '%' order by schoolname

Answer 4

Remember to always use two quotes when using apostrophes like ''

Here is a straightforward query:

select schoolname from school where schoolname like 'children''%'

If you are passing @filter as a variable, you can do the following:

declare @filter nvarchar(20)

select CONCAT(schoolname, CASE WHEN schoolcity IS NULL OR schoolcity = '' THEN '' Else  (', ' + REPLACE(schoolcity,'ã','a')) END,CASE WHEN schoolname IS NULL OR schoolstate = '' THEN '' Else (', ' + schoolstate) END) as schoolname from schools where schoolname like '%' + @filter + '%' order by schoolname

SQL query encountering issue after introducing apostrophe symbol ''`

Answer №1

Answer №2

Similar questions

Error: The identifier has already been declared and cannot be re-declared

Looking to divide a string into smaller parts without using the split function

Displaying Elements of a JSON Array using v-for

When loading JSON data dynamically into a D3 network visualization, the links may not be immediately visible

Bootstrap 4 rows causing dropdown clipping

What is the reason for the inclusion of [Circular] in the hash?

Error: CSS and JavaScript files cannot be found

Changing color in JQuery based on class and the content of HTML elements

The compatibility issue between Rails 7 and Bootstrap 5.2.3, along with importmaps JavaScript, is causing dysfunction in the

Passing Optional Parameters to AngularJS Modal

Creating multiple div columns dynamically with jQuery

I am seeking to perform these operations solely using pure WebGL, without relying on the Three.js library

The error message "Each item within a list must be assigned a unique 'key' prop" is being displayed

String representation of a failed test

What is the method for obtaining the present date in C#?

JS: Best way to transfer selected information to the following page?

Steps for displaying a division on clicking a hyperlink

Strengthening JavaScript Security

The schema does not accept fileLink as valid

Discover a way to retrieve the index of elements in Vue JS that are not within a v-for loop