How to query rows matching for a particular string?
sql query that uses partial matching in the where clause
pandas select rows containing string
sql query string
mysql match any word in string
This is how table looks like and here Empid and idnumber were unique for each employee and also an employee can have multiple badges with different badge number.
Now,I want to filter employees whose badge start with 6542 and 3214, ie employees carrying both badge starting from 6542 and 3214
There are some records which is having only a single badge starting from 6542 or 3214 . but I want only employees who is carrying both badges .
GROUP BY, use
HAVING to ensure both badges:
select empid, name from Table where badge like '6542-%' or badge like '3214-%' group by empid, name having count(distinct badge) > 1
select empid, name from Table where badge like '6542-%' intersect select empid, name from Table where badge like '3214-%'
sql find rows partially matching a string, This matching all rows what contains exactly the specified words. In your Because the sql engine does not use indexes in regexp queries. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more How to query rows matching for a particular string?
Just use like on badge field
Select empid, name from TableName where badge like '6542%' or badge like '3214%' group by empid, name having count(*)>1
Matching character strings in the WHERE clause, The LIKE keyword indicates that the following character string is a matching you should enclose the match strings and character strings in single quotes. The query only returns rows where the whole clause is true. For example, this gets the row that stores "Sir Stripypants" in the column toy_name: select * from toys where toy_name = 'Sir Stripypants'; A condition can match many rows. The database will return all of them. The rows for Sir Stripypants & Mr Bunnykins both have the colour red.
this will work:
select empid from table_name where regexp_like(Badge,'^(6542)(.*)$') intersect select empid from table_name where regexp_like(Badge,'^(3214)(.*)$');
sql server equivalent:
select empid from table_name where PATINDEX ('^(6542)(.*)$',Badge) !=0 intersect select empid from table_name where PATINDEX ('^(6542)(.*)$',Badge) !=0
Matching Patterns with LIKE, You can use LIKE to retrieve rows based on partial information. Matches a string of length ≥ 1 that ends with s, including the single letter s. Show rows contain a specific string by Filter function. To display rows that contain a string, you can do as below: 1. Select the ranges you use, and click Data > Filter to enable the Filter function. 2. Then click at the filter icon on the column you want to filter on, and select Text Filters > Contains. See screenshot: 3.
We can use
COUNT DISTINCT with
HAVING as below
DECLARE @test AS TABLE(EMPID INT, Badge VARCHAR(50), idNumber INT, EName VARCHAR(50)) INSERT INTO @test VALUES (1148, '6542-74488', 66448, 'Adam Jhon'), (1148, '642-8562', 66448, 'Adam Jhon'), (1148, '3214-52874', 66448, 'Adam Jhon'), (1149, '3214-45220', 209541, 'Tom Koyaski'), (1150, '3214-23134', 63339, 'Shirin Abdulla'), (1151, '3214-42355', 65498, 'Linda Jhon'), (1151, '6542-2546', 65498, 'Linda Jhon'), (1152, '3214-47632', 208673, 'Gayeth'), (1153, '6542-73085', 83209, 'Maria Smith'), (1153, '3214-58073', 65498, 'Maria Smith'), (1154, '3214-26735', 208673, 'Ayan Jacob'), (1155, '642-26739', 53959, 'Wo Li') SELECT empid, Ename FROM @test WHERE badge LIKE '6542%' OR badge LIKE '3214%' GROUP BY empid, Ename HAVING COUNT (DISTINCT(CASE WHEN badge like '6542%' THEN 1 WHEN badge LIKE '3214%' THEN 2 END))>1
empid Ename 1148 Adam Jhon 1151 Linda Jhon 1153 Maria Smith
Pandas: Select rows that match a string, Micro tutorial: select rows of a Pandas DataFrame that match a (partial) string. Pattern matching is a versatile way of identifying character data. In SQL, the LIKE keyword is used to search for patterns. Pattern matching employs wildcard characters to match different combinations of characters. The LIKE keyword indicates that the following character string is a matching pattern. LIKE is used with character data.
just simply do like this
Select a.empid, a.name from TableName as a inner join TableName as b on a.Empid = b.Empid and a.idnumber = b.idnumber and b.badge like '3214%' where a.badge like '6542%'
CONTAINS (Transact-SQL), For the query to return any rows, property_name must be specified in the This query would match the following string, in which the total In the below sample query, I’ve concatenated multiple rows of the column “CountryName” to a single string and added a comma between the country names. Then using a substring function, I’m removing the leading comma.
MySQL LIKE operator string function, Example of MySQL LIKE operator with wildcard (%) matching from the beginning. The following MySQL statement will return those rows from the table author in $password); foreach($dbh->query('SELECT * FROM author WHERE table author in which the name of the author ends with the substring 'on'. I have copied this particular table recently, so I know the table, structure and keys are identical in all 3 places. SELECT * FROM \accounting` WHERE type='cash' AND status='pending'Response on 5.7: (Query took 0.0427 seconds.)Response on 8.0 dev db: (Query took 0.7911 seconds.)Response on 8.0 prod db: (Query took 4.1671 seconds.)`
SQLite LIKE, This tutorial shows you how to use SQLite LIKE operator to query data based The underscore _ wildcard matches any single character. The percent sign % wildcard examples. The s% pattern that uses the percent sign wildcard ( % ) matches any string However, it returns rows whose values in the c column contains 10: Pattern Matching on Indexed Columns. When you use LIKE to search an indexed column for a pattern, Oracle can use the index to improve performance of a query if the leading character in the pattern is not % or _. In this case, Oracle can scan the index by this leading character.
SQLite WHERE - Filter Rows in a Result Set, This tutorial shows you how to use SQLite WHERE clause to filter rows in a result In this example, you add a WHERE clause to the SELECT statement to filter rows returned by the query. LIKE, returns 1 if a value matches a pattern In case you compare values in different data types e.g., a string with a number, SQLite Vectors (ordered collections of numbers/strings) and data.frames (“table”-like objects which handle rows as observations and columns as variables), are the most common data objects in R. A vector can only contain a single data type (i.e. a vector is either all numbers, or all strings, or all logical values, etc.).
- GROUP BY, HAVING, COUNT DISTINCT
- better put text than images
- @coffemug, which one?
- both of them ….
- If you had sample data as formatted text I could copy and paste and try for myself, but with an image that can't be done.
select empid, name from Table where badge like '6542-%'return anything?
- it returns all the records starting from 6542%
- "employees carrying both badge starting from 6542 and 3214"
- This will return also employees whos carrying one badge
- ah ok, added goup by so
- Looks fine to me.
- its not working as its returning employees having only badges starting from 6542% or 3214 .
- "both badge" wanted.
- @jarlh now its ok
- its not supported I am in ms sql server