How to search for a word between '' in SQL Table-CodePudding

What I'm trying to do is to search for a word in SQL between two ' like 'test' but it won't work using:

SELECT id From TestTable WHERE Test LIKE '%'test'%'

CodePudding user response：

The following SQL statement selects all id from TestTable Where the Test column has "test" in any position:

SELECT id From TestTable WHERE Test LIKE '%test%';

Let's say if the text in TestTable is this: "this is a simple 'test'"

and you are looking for 'test' (not test) then your query will look like this:

SELECT id From TestTable WHERE Test LIKE "%'test'%";

As per my experience, sometimes outer quotes conflict with inner quotes. So I prefer To use different quotes for the outer and inner sides in like statement.

CodePudding user response：

Oracle
Here you can see the differences:

WITH
    a_tbl AS
        (
            Select 1 "ID", 'This is a test string 1' "TEST_STRING" From Dual Union ALL
            Select 2 "ID", 'This is a ''test'' string 2' "TEST_STRING" From Dual Union ALL
            Select 3 "ID", 'This is a "test" string 3' "TEST_STRING" From Dual Union ALL
            Select 4 "ID", 'This is a string 4 - without magic word' "TEST_STRING" From Dual Union ALL
            Select 5 "ID", 'This is a string 5 - just for testing' "TEST_STRING" From Dual 
        )
/*  Sample data
        ID TEST_STRING                           
---------- ---------------------------------------
         1 This is a test string 1                 
         2 This is a 'test' string 2               
         3 This is a "test" string 3               
         4 This is a string 4 - without magic word 
         5 This is a string 5 - just for testing  
*/


Select * From a_tbl Where TEST_STRING Like '%test%'
/*  Result
        ID TEST_STRING                           
---------- ---------------------------------------
         1 This is a test string 1                 
         2 This is a 'test' string 2               
         3 This is a "test" string 3               
         5 This is a string 5 - just for testing       
*/

Select * From a_tbl Where TEST_STRING Like '%''test''%'
/*  Result
        ID TEST_STRING                           
---------- ---------------------------------------
         2 This is a 'test' string 2             
*/

Select * From a_tbl Where TEST_STRING Like '%"test"%'
/*  Result
        ID TEST_STRING                           
---------- ---------------------------------------
         3 This is a "test" string 3             
*/

Note that '%test%' will select rows containing something like testing or testament and if you want just the word test then use surrounding spaces LIKE '% test %'