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
Sql Join: Selecting the Last Records in a One-To-Many Relationship
How to Convert Milliseconds to Time(Hh:Mm:Ss) in Oracle
Postgresql Error: Relation Already Exists
Removing Leading Zeroes from a Field in a SQL Statement
How to Use Return Value of Insert...Returning in Another Insert
Find Employee Name Who Is Having Highest Salary in Given Month in SQL
Check If a Time Is Between Two Times (Time Datatype)
How to Calculate Date Difference in Postgresql
Postgresql Convert Columns to Rows Transpose
Delete Rows With Date Older Than 30 Days With SQL Server Query
Create a Query That Will Display Employee Name and Department Number
Make SQL Select Same Row Multiple Times
Select Only Rows With Max Date
The MySQL Service on Local Computer Started and Then Stopped
Multiple Query Same Table But in Different Columns MySQL
Job for Mysqld.Service Failed See "Systemctl Status Mysqld.Service"
Mysql Error 1449: the User Specified as a Definer Does Not Exist