How to get data from table based on a particular character in SQL Server

Using CHARINDEX() function. Let us assume, I have a table with many rows in it. I want to filter out (or get) only data based on a particular character, like "?". See the below image.

image

filter out data from table based on a particular value

Let's see the query first.

SELECT *from dbo.Sample_table WHERE CHARINDEX('?', SUBJECT) > 0

The CHARINDEX() function searches for a substring in a string and returns a number (a numeric) value, which is the position of the substring in the string. And, if its not a match, it returns 0.

So, in the above query, if the query finds a match, then it will return the position of the substring (in our case its "?").

The CHARINDEX() takes three parameters. Although, I have used only two.

• search_expression - The value that we want to search. This is Required.

• expression_to_be_searched - The "string" in which the value will be searched. In our case, its the column name, in which we'll find the "substring". This is Required.

• start_location - Its takes an integer value, which is the location from where it will start looking into. This is Optional. If no value is assigned, it will start looking (searching) from the 1st location till the end of the string or sentence.

The CHARINDEX() function can be used in many other ways. I have explained about the function in detail with few useful examples here in this tutorial.

← PreviousNext →