CODEWITHSUNDEEP
sqlsql-server
RobertKing
RobertKing

Reputation: 1921

sql query to find a column value with specific text available

Is their any other way to find a column values with the specified text available in it.

i know their is one way like,

SELECT COLUMN_NAME FROM TABLE_NAME
WHERE COLUMN_NAME LIKE '%sample_text%'

and i tried 

SELECT COLUMN_NAME FROM TABLE_NAME
WHERE CONTAINS(COLUMN_NAME,'sample_text')

also but it requires table to be full-text indexed

Upvotes: 2

Views: 31706

Answers (2)

Ravi Singh
Ravi Singh

Reputation: 2080

Query 1 

select 1 where patindex('%find_string%','find_stringfdasfsa')> 0

Query 2

select 1 
where  len(REPLACE('find_stringfdasfsa','find_string',''))<LEN('find_stringfdasfsa')

Query 3

select 1 
where CHARINDEX('find_string','find_stringfdasfsa') > 0

Execution plan is same as Like. You will have to check the performance.

Upvotes: 0

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

You can use PATINDEX():

SELECT COLUMN_NAME FROM TABLE_NAME
WHERE PATINDEX('%sample_text%', COLUMN_NAME) != 0

Upvotes: 1

Related Questions