w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Use SQL to return table rows that match multiple search criteria
Besides your being exposed to SQL injection, here is what your query looks like when Request["search"].IsEmpty() : SELECT * FROM Items WHERE '%%' is null (...) ; The string '%%' is not null (it is a two-characters string). Instead you want to so something like this: string sql = "SELECT * FROM Items WHERE '" + search + "' = '' (...)" ; So that sql becomes: SELECT * FROM Items WHERE '' = '' (...) Alternatively you could drop the test '' = '' altogether, because '%%' is in fact LIKE any string: string sql = "SELECT * FROM Items WHERE '%" + search + "%' LIKE item_name"; If Request["search"].IsEmpty() then your query looks like this: SELECT * FROM Items WHERE '%%' LIKE item_name (...) -- always true The above is only meant for educational purpose, because it produces sub-optima

Categories : Asp Net

Return rows and columns from a 2d array using values from a 1d array in Numpy
Is this what you want? >>> a = array([[1, 0, 0, 0, 0, 0], ... [0, 4, 0, 0, 0, 1], ... [0, 0, 3, 0, 0, 0], ... [0, 1, 0, 7, 0, 10], ... [0, 0, 0, 0, 8, 0], ... [0, 2, 0, 0, 0, 9]]) >>> >>> a[1::2,1::2] array([[ 4, 0, 1], [ 1, 7, 10], [ 2, 0, 9]]) Since your stride access is so regular, you can accomplish this with basic slicing. As for the mask: >>> a = np.ones(a.shape,dtype=bool) >>> a[1::2,1::2] = False >>> a array([[ True, True, True, True, True, True], [ True, False, True, False, True, False], [ True, True, True, True, True, True], [ True, False, True, False, True, False], [ True, True, True, True, True, True], [ True, Fa

Categories : Python

Match rows and subtract columns
This is kinda a common problem. One way to do it in base R would be to use match as you suggest, like this with no apply in sight.... # rows of bigdf that appear in smalldf, in order that they appear in smalldf idx <- match( rownames(smalldf) , rownames(bigdf) ) # subtract rows of smalldf from bigdf for rows that appear in smalldf and rbind them with original rows from bigdf that do not appear in samlldf result <- rbind( ( bigdf[ idx , ] - smalldf ) , bigdf[ -idx , ] ) # Order the results result <- result[ order( rownames(result) ) , ] X Y Z A 3 2 5 B 10 3 7 C 0 0 6 D 5 3 4 E 9 -2 20

Categories : R

Search column for specific text and display any/all rows that contain a match
On Worksheet 1 make sure you set column headers with no duplicates. Copy the column headers of the columns that you need to first row on Worksheet 2. Lets say you copy 4 columns names, then on cell E1 you write the 5th column header from worksheet 1, and on cell E2 you write the Operating System name make sure you're on worksheet 2. go to Data> Filter> Advanced Filter. Select Copy to another location on List range: select the table from Worksheet 1 (eg: A1:G100) on Criteria range: select cells E1:E2 from Worksheet 2 On Copy To: select column names from worksheet 2 (EG: A1:D1) Click OK

Categories : Excel

Query to return rows that match a block of ip addresses?
I would first check for full IP address, then mask out the last byte with *, and check for that, then the last 2 bytes, and so on. This way you go from specific to less specific, and if once you get a hit, the IP is blocked. It's probably not the most efficient way, but I guess you have to do it only once, at login-time. I would store IP addresses for simplicity as strings. Another solution could be to store IP address as byte-array, but that wouldn't make anything easier I guess, and would be much less maintainable with for example the command-line tool SQL tool.

Categories : PHP

Excel - how to look up a table for two values and return another value if these two values match
I would think a PivotTable may solve your problem. Step 1: Create a PivotTable. Step 2: Use Index - Match and Array formula to get the matching of the two 'criteria'. Link: https://docs.google.com/spreadsheet/ccc?key=0AqU9Z4LZATBedHdDVFpVcmVzU2JQWGtyajltakk5WWc&usp=sharing

Categories : Excel

Match statement spaced words return results from multiple columns
Unless you are really looking through every field, (i.e. John Stevenson would be considered a match on 'Steve') you can do this much simpler... select * from contacts where firstname like '%Steve%' and (primary_emailaddress like '%email@email.com%' or alternate_emailaddress like '%email@email.com%')

Categories : Mysql

Search Strings for Wildcard in python and return position of match
I'm not very experienced in Python, but I think you can do something like this: import re strings = [['ID#1','NTGSLTKNASMNLTQRSNQT'],['ID#2','NLSHTNWEUWBNTTDKWODNUT']] def findpos(s): return [s[0], [m.start() + 1 for m in re.finditer('N[^P][ST]',s[1])]] return map(findpos, strings) // [['ID#1', [8, 12, 18]], ['ID#2', [1, 12, 20]]] or even more simply, just: [[s[0], [m.start() + 1 for m in re.finditer('N[^P][ST]',s[1])]] for s in strings]

Categories : Python

Return distinct rows where there is at least 1 discrepancy in certain columns
Join the table to itself to find other rows that match COL_A but have different COL_B or COL_C: select distinct t1.COL_A, t1.COL_B, t1.COL_C from MyTable t1 join MyTable t2 on t1.COL_A = t2.COL_A AND (t1.COL_B != t2.COL_B OR t1.COL_C != t2.COL_C) See this working on SQLFiddle If you want the rows ordered, add an order by at the end. With an index on (COL_A) this would perform very well. With a compound index on (COL_A, COL_B, COL_C) this would perform extremely well.

Categories : SQL

Powershell - search through CSV and return only rows with specific error
Try this way, this will check for $ErrorMessage1 in the ID or Error columns: Import-csv X:errorlist.csv | Where-Object {$_.Error -like "*$ErrorMessage1*" -or $_.ID -like "*$ErrorMessage1*"}

Categories : Powershell

subtract values in different columns and different rows
If this is SQL Server you can use DATEDIFF(): select a.job_nm, datediff(day, a.start_dt, b.end_dt) as [difference] from JOB a,b where a.job_nm='ggggg' and b.job_nm='iiiii'

Categories : SQL

How to loop through multiple rows to find a value, and then return only CERTAIN columns found in that row in VBA Excel
Your reference to the range on your Data1 worksheet is incorrect. Update Worksheets(Data).Range("C:O" & i + 2).Copy Destination:=_ Worksheets(Sales).Range("A2") to... Worksheets(Data).Range("C" & i + 2 & ":O" & i + 2).Copy Destination:=_ Worksheets(Sales).Range("A2") and it should work. Note, however, that every time this codes runs, it will paste the found value from the Data sheet into the same location on the Sales sheet (cell A2), so just a guess, but you may want to update this reference as well. (e.g. Worksheets(Sales).Range("A" & i + 1))

Categories : Excel

Google scripts search spreadsheet by column, return rows
You can use the code below to search in a specific column. Code is self explanatory. function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ {name: "Search", functionName: "onSearch"} ]; ss.addMenu("Commands", menuEntries); } function onSearch() { var searchString = "Test11"; var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName"); var column =4; //column Index var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues(); //1st is header row var searchResult = columnValues.findIndex(searchString); //Row Index - 2 if(searchResult != -1) { //searchResult + 2 is row index. SpreadsheetApp.getActiveSpreadsheet().setActiveRange(sheet.getRange(searchResult + 2, 1))

Categories : Search

Index/Match to Return Multiple Values
I'm not sure what you are looking for can be done with worksheet formulas alone. Here is a short VBA routine that should do it. Function MatchConcat(LookupValue, LookupRange As Range, ValueRange As Range) Dim lookArr() Dim valArr() Dim i As Long lookArr = LookupRange valArr = ValueRange For i = 1 To UBound(lookArr) If Len(lookArr(i, 1)) <> 0 Then If lookArr(i, 1) = LookupValue Then MatchConcat = MatchConcat & ", " & valArr(i, 1) End If End If Next MatchConcat = Mid(MatchConcat, 3, Len(MatchConcat) - 1) End Function The code is a user-defined function that can be used in the worksheet. The function takes three arguments: the value you want to match, the column of values that you want

Categories : Excel

How do i use a Fabrik (joomla component) form to connect to database table & search for match & return a result?
I have no problem using the following setting I think you will need to add JRequest::getVar before ('you_element') and have the setting to be onBeforeProcess $db = JFactory::getDbo(); $date = date("Y-m-d H:i:s", time()); $value = JRequest::getVar('off_line_ap___offline_reason'); $user =& JFactory::getUser(); $uid = $user->id; $db->setQuery("INSERT INTO d2_exchange_log (date_time, part, quantity, user_id, purpose) VALUES ('{$date}', '{$value}', '99','{$uid}', 'Test')"); $submissionsNo=$db->loadResult();

Categories : PHP

Same values being returned for multiple rows in sql but not all columns
Its because your TotalUsers, ActiveUsers and SuspendedUsers queries are all using their own (unrestricted) copy of the Company table, whereas your TotalLogin is using the main instance from which you're selecting. This means that the TotalLogin numbers you're seeing are for that particular company, but the other fields are across ALL companies. Presumably you wanted something more like: SELECT company.companyStatus, company.CompanyId, company.companyName, count(distinct u.UserID) TotalUsers, sum(case when u.userstatusid =2 then 1 else 0 end) ActiveUsers, sum(case when u.userstatusid = 3 then 1 else 0 end) SuspendedUsers, count(distinct u.usersessionid) TotalLogin from Company inner join Users on Users.CompanyID = Company.CompanyId

Categories : SQL

Crystal Reports: Putting values from several rows into their own columns?
This is labelled "sql", so I assume a SQL solution is ok. The following query pivots the four columns: select SalesOrderId, max(case when seqnum = 1 then hold end) as hold1, max(case when seqnum = 2 then hold end) as hold2, max(case when seqnum = 3 then hold end) as hold3, max(case when seqnum = 4 then hold end) as hold4 from (select s.*, row_number() over (partition by SalesOrderId order by ReleaseDate) as seqnum from (<your query>) s ) s group by SalesOrderId; I'm guessing what the field names are, but the structure you need would be similar to this.

Categories : SQL

Matlab: removing rows when there are repeated values in columns
The trick here is how to find the rows with repeated values in an efficient manner. How about this: % compare all-vs-all for each row using `bsxfun` >> c = bsxfun( @eq, A, permute( A, [1 3 2] ) ); >> c = sum( c, 3 ); % count the number of matches each element has in the row >> c = any( c > 1, 2 ); % indicates rows with repeated values - an element with more than one match >> A = A( ~c, : ) A = 2 4 6 8 4 8 7 6 8 5 4 6

Categories : Performance

How to select rows from a data frame in which values in a given columns are not None?
Use dropna(): df.dropna(subset=['colname']) or alternatively Series.notnull: df[df['colname'].notnull()]

Categories : Python

return all possible combinations of values in columns in SQL
As the link you provided had a good answer, modified below select distinct t1.C1, t2.C2, t3.C3, t4.C4 from MyTable t1, MyTable t2, MyTable t3, MyTable t4

Categories : Sql Server

Deleting rows and columns in matrix based on values in diagonal in R
Use the diag function and logical comparison with < and the [ subset operator like this... idx <- ! diag(m) < 5 m <- m[ idx , idx ] e.g. m # x1 x2 x3 x4 #x1 13 15 12 25 #x2 23 4 23 34 #x3 21 11 1 11 #x4 11 22 23 33 idx <- ! diag(m) < 5 m <- m[ idx , idx ] m # x1 x4 #x1 13 25 #x4 11 33

Categories : R

LINQ - Static columns transposition into aggregate rows values
It's a kludge, but this should work: var matches = ( from entry in table where entry.StockIndexId == StockIndexId where Companies.Contains(entry.StockCompanyId) select entry).ToList(); var ResponseValues = new List<DTO>(); ResponseValues.Add(new DTO { Quarter = 0, AggregatedValue = matches.Sum( match => match.Q0 ?? 0 ) / matches.Length }); ResponseValues.Add(new DTO { Quarter = 1, AggregatedValue = matches.Sum( match => match.Q1 ?? 0 ) / matches.Length }); ... snip ... ResponseValues.Add(new DTO { Quarter = 20, AggregatedValue = matches.Sum( match => match.Q20 ?? 0 ) / matches.Length });

Categories : C#

TSQL: Join columns, but rows in one column have multiple values
try select tab2.* -- whatever from t1 tab1 inner join t2 tab2 on ( ';'||tab2.col2||';' like '%;'||tab1.col1||';%' ) ; the extra affixed ; characters serve to avoid disjunctions in the join condition.

Categories : Sql Server

R: aggregate and count rows that match a condition, group by unique values and transform table
I think you mean how many times appears the product and not how many different products , for each ID. Here a solution using data.table and reshape but in 2 steps : First I compute the number of product in the long format , then I transform my data to the wide one. I think that using plyr and ddply it is better here. library(data.table) DT <- as.data.table(dat1) DT[,n := .N,by= ID] reshape(DT,direction='wide',idvar='ID',timevar='Product',drop='Store') ID Purchase_date.Product_A n.Product_A Purchase_date.Product_C n.Product_C 1: 47 01-01-2012 2 NA NA 2: 26 NA NA 01-17-2012 1

Categories : R

Split characters in column names to new columns with logical values in rows
Here is an approach using replicate and Map as.data.frame(Map(x = strsplit(names(mydf), '[.]+'), DATA = mydf, f = function(x,DATA){ setNames(replicate(length(x), DATA, simplify = FALSE),x )} )) ## author Aa Ab BB Ca Cb ## 1 N1 TRUE TRUE TRUE FALSE FALSE ## 2 N2 TRUE TRUE FALSE FALSE FALSE ## 3 N3 FALSE FALSE TRUE TRUE TRUE

Categories : R

Compare 2 columns from different tables and return different values of 2nd table
Sounds like something you should do directly in the query. Your question is a bit unclear, bit if you want to get all rows from table 1 whose ids are not stored in table 2, you can do this: SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2)

Categories : PHP

Search MongoDB to return all values for a parameter
How about using a distinct query: db.products.distinct("designer") db.collection.distinct()

Categories : Javascript

Insert into table where values have sub queries and return multiple rows
You want to use insert . . . select, not insert . . . values. The following inserts all pairs that are equivalent to "bob": INSERT INTO `table_values` (`user_id`, `otherUser_id`) Select `id` FROM `users` u1 join `users` u2 on u1.value_1 = u2.value_2 WHERE u1.`username` = 'bob' and u2.username <> 'bob'; The following inserts them where they do not already exist: INSERT INTO `table_values` (`user_id`, `otherUser_id`) Select u1.`id`, u2.id FROM `users` u1 join `users` u2 on u1.value_1 = u2.value_2 WHERE u1.`username` = 'bob' and u2.username <> 'bob' and not exists (select 1 from table_values where u1.id = user_id and u2.id = otherUser_id); I'm not sure if you want to check them in a different o

Categories : Mysql

I need a VBA code to count the number rows, which varies from ss to ss, return that number and copy and paste that row and all other columns
This should give you the last row containing data: ActiveSheet.UsedRange.Rows.Count This will give you the last row in a specific column: Range("B" & Rows.Count).End(xlUp).Row here is an example of how I can copy every row in the first three columns of a worksheet Sub Example() Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count Range(Cells(1, 1), Cells(LastRow, 3)).Copy Destination:=Sheet2.Range("A1") End Sub You have to be careful as there are some caveats to both methods. ActiveSheet.UsedRange may include cells that do not have any data if the cells were not cleaned up properly. Range("A" & Rows.Count).End(xlUp).Row will only return the number of rows in the specified column. Rows(Rows.Count).End(xlUp).Row will only return the number of rows

Categories : Vba

In a 2d array. Iterate for the first 2 rows and 2 columns, then first 2 rows again, next 2 columns etc
Iterate rows r = 0;(r +1) < r_max; r+=2 Iterate colums c = 0; (c+1) < c_max; c +=2 operate on r, r+1 and c,c+1 If you feel the "2" is hardcoding, you can easily paramerize it, but the "operate" stage might need 2 more loops for that depending on what you want to do. Otherwise this is quite fine. I would just add checks or asserts on the top which verify what you say are your assumptions

Categories : Java

Sticky header columns do not match table columns
This solved my question. /* apply a natural box layout model to all elements */ *, *:before, *:after { -moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box; }

Categories : Javascript

Javascript string.match refuses to return an array of more than one match
You're looking for the form needle.exec(haystack) From my console: > haystack = "{List:[Names:a,b,c][Ages:1,2,3]}"; "{List:[Names:a,b,c][Ages:1,2,3]}" > needle = /^{List:[Names:([a-zA-z,]*)][Ages:([0-9,]*)]}$/g ; /^{List:[Names:([a-zA-z,]*)][Ages:([0-9,]*)]}$/g > needle.exec(haystack); ["{List:[Names:a,b,c][Ages:1,2,3]}", "a,b,c", "1,2,3"]

Categories : Javascript

Efficient grouping of rows from Nx2 integer numpy array to match sequential elements in rows
If you want to find a longest "closed loop", than I think you problem is NP-complete (finding a longest simple cycle in an undirected graph). if you want to find an arbitrary cycle, try depth first search - it's about 0.02s for 1000 elements: from collections import defaultdict def ordered(data, N): edges = defaultdict() for v1, v2 in data: edges.setdefault(v1, []).append(v2) edges.setdefault(v2, []).append(v1) visited = [False] * N path = None for v in range(N): if not visited[v]: path = dfs(edges, visited, v) if path is not None: break if path is not None: return [[path[i], path[i + 1]] for i in range(len(path) - 1)] def dfs(edges, visited, v1, vp=None): path = [v1] if visited[v1]: return path

Categories : Python

MySQL: Use search results (Like %search%), to match id's in another table in the database
I would perhaps try a combination of LOCATE() and SUBSTR(). I work mainly in MSSQL which has CHARINDEX() that I think works like MySQL's LOCATE(). It is bound to be messy. Are there a variable number of elements in the parts-list field?

Categories : PHP

Convert rows in table to columns for displaying into Header columns for Gridview
Assuming you are working with MS SQL server you need to use the PIVOT keyword. Link for the same: PIVOT AND UNPIVOT

Categories : C#

Mysql query to dynamically convert rows to columns on the basis of two columns
If you had a known number of values for both order and item, then you could hard code the query into: select id, max(case when `order` = 1 then data end) order1, max(case when `order` = 2 then data end) order2, max(case when `order` = 3 then data end) order3, max(case when item = 1 then price end) item1, max(case when item = 2 then price end) item2, max(case when item = 3 then price end) item3, max(case when item = 4 then price end) item4 from tableA group by id; See Demo. But part of the problem that you are going to have is because you are trying to transform multiple columns of data. My suggestion to get the final result would be to unpivot the data first. MySQL does not have an unpivot function but you can use a UNION ALL to convert the multiple pairs of columns into

Categories : Mysql

Turning a Data.Frame with 28441 Rows in it to a Dataframe with 28 Rows and 1000 Columns in R
Not sure what language this is coded in but I think I understand your problem. If your original is all in the first column of zip.codes(I assume this is the old dataframe), you want [i,1] meaning row i, first column. Your j should go from 1 to for (i in 1:length(zip.codes)) { k = i % 1000 for(j in 1:1000) { new.zip.codes[k,j]<-zip.codes[i,1] } } Using the % as a modulo operator, you can pick out the row number you want, had have j as your column control. This is extremely useful when you know your fixed table size. Hope this helps.

Categories : R

R: How select rows form a csv, that match rows of another csv in R?
## Read csv files file1 = read.csv(paste(path, "file1.csv", sep = ""), stringsAsFactors = FALSE, na.strings = "NA") file2 = read.csv(paste(path, "file2.csv", sep = ""), stringsAsFactors = FALSE, na.strings = "NA") #> file1 # Species Longitude Latitude #1 Cat 0.4300052 0.04554442 #2 Dog 0.6568743 0.53359425 #3 Fish 0.8218709 0.20328321 #4 Deer 0.4601183 0.93191142 #5 Cow 0.9975495 0.02349226 #> file2 # Species #1 Fish #2 Dog ## Get subset in first file of species in second file result = file1[file1$Species %in% file2$Species,] You get: #> result # Species Longitude Latitude #2 Dog 0.6568743 0.5335943 #3 Fish 0.8218709 0.2032832

Categories : R

copy columns and rows based on two criteria in two columns
Although I am not sure I understand your question, you can filter on columns H and AC by changing the field references in the two Autofilter statements to Field:=8 and Field:=29, respectively.

Categories : Excel

Does Youtube API have "AND and OR" search and explicit match search?
And is supported with include and exclude just like the search query in the Web UI. You can use -{query term} to exclude a query term. Or |{gaga} to OR. like {lady -gaga} or in decoded form https://www.googleapis.com/youtube/v3/search?part=snippet&q=lady+-gaga&key={YOUR_API_KEY} You can also make separate calls, put results into sets and do all these operations in your client.

Categories : Ruby On Rails



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.