Check If a Column Contains Text Using SQL

Check if a column contains text using SQL

Leaving database modeling issues aside.
I think you can try

SELECT * FROM STUDENTS WHERE ISNUMERIC(STUDENTID) = 0

But ISNUMERIC returns 1 for any value that seems numeric including things like -1.0e5

If you want to exclude digit-only studentids, try something like

SELECT * FROM STUDENTS WHERE STUDENTID LIKE '%[^0-9]%'

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
OR column1 LIKE '%word2%'
OR column1 LIKE '%word3%'

If you need all words to be present, use this:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
AND column1 LIKE '%word2%'
AND column1 LIKE '%word3%'

If you want something faster, you need to look into full text search, and this is very specific for each database type.

Check if the array of text column contains string containing phrase

I would recommend:

select c.*
from customers c
where exists (select 1
from unnest(c.products) p
where p like '%a%'
) and
not exists (select 1
from unnest(c.products) p
where p like '%b%'
);

You can do this with a single unnest. It just seems more complicated:

select c.*
from customers c
where (select countif(p like '%a%') = count(*) and
countif(p like '%b%') = 0
from unnest(c.products) p
where p like '%a%' or p like '%b%'
) ;

How to check if a column contains a substring of string in SQL?

You just flip the two terms in your LIKE operator:

SELECT * 
FROM mytable
WHERE 'words' LIKE CONCAT('%',name,'%')

I believe that LOCATE() and INSTR() may work here too which looks nicer since there isn't a need for concatenating the search term/substring.

SELECT *
FROM mytable
WHERE INSTR('words', name) > 0

SQL check if column contains specific values

One method is aggregation with having:

select id
from t
where values in ('a', 'b', 'c')
group by id
having count(distinct values) = 3;

If you wanted more flexibility with the counts of each value:

having sum(case when values = 'a' then 1 else 0 end) >= 1 and
sum(case when values = 'b' then 1 else 0 end) >= 1 and
sum(case when values = 'c' then 1 else 0 end) >= 1

SQL: how to check if column contains letters in it

PostgreSQL supports POSIX regular expressions:

SELECT applications.ic
FROM applications
WHERE applications.ic ~ '^\d+$'

The regular expression here is:

  • ^ — matches the beginning of the string
  • \d — matches a digit
  • + — one or more modifier applied to the previous matcher
  • $ — matches the end of the string

SQL - Query to find if a string contains part of the value in Column

The answer would be "use LIKE".

See the documentation: https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

You can do WHERE 'string' LIKE CONCAT(column , '%')

Thus the query becomes:

select * from t1 where 'ABCDEFG' LIKE CONCAT(column1,'%');

If you need to match anywhere in the string:

select * from t1 where 'ABCDEFG' LIKE CONCAT('%',column1,'%');

Here you can see it working in a fiddle:
http://sqlfiddle.com/#!9/d1596/4

Check if CSV string column contains desired values

Convert to an array, and use the overlap operator &&:

SELECT *
FROM tbl
WHERE string_to_array(role, ':') && '{0,1}'::text[];

To make this fast, you could support it with a GIN index on the same expression:

CREATE INDEX ON tbl USING GIN (string_to_array(role, ':'));

See:

  • Can PostgreSQL index array columns?
  • Check if value exists in Postgres array

Alternatively consider a proper one-to-many relational design, or at least an actual array column instead of the string. Would make index and query cheaper.



Related Topics



Leave a reply



Submit