Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

sql LIKE operator for sampling

Bogopo last edited by

There are "massiv" values: "Izumrud," "Fianit," "Granat," "Rauchtopaz." In the OBD field vstavki, the values are presented lines:
id vstavki
1 grenades, tops, ametist, Rahutopaz, chrysolite
2 london-toaz, Phianiti
3 ametist, citrin, grenades, tops, filanit
4 ametres, Phianiti
5 tops, Pianiti
etc.
How do you write an expression for LIKE to remove all the lines in which there is at least one word in the body?
Reply Quote 0
1 Reply Last reply
K
kalyn last edited by
Eat through. OR:
```
WHERE (vstavki LIKE "%изумруд%"
   OR vstavki LIKE "%фианит%"
   OR vstavki LIKE "%гранат%"
   OR vstavki LIKE "%раухтопаз%") AND...;
```
You can get a line like like that in any CP.
And the regular in the muscle is this. http://dev.mysql.com/doc/refman/5.7/en/regexp.html ♪
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

N

Request for SQL as to add sample results to the third
Software Programming • sql server sql • • Nykeriab

2

0
Votes

2
Posts

0
Views

R

First of all, I'd advise you to put in a transaction. What you're looking for is like this:SELECT halfe1 from Join, halfe2 from Join, NEWID( INTO Class3Class3s_Class1Class1s1s
J

How to make a sql request for a sample of no data originally available (T-SQL)
Software Programming • sql server sql • • jules

2

0
Votes

2
Posts

0
Views

WITH cte AS ( SELECT DATEADD(DAY, n.number, @min_date) AS ShiftDate FROM master..spt_values n WHERE n.type = 'P' AND n.number <= DATEDIFF(DAY, @min_date, @max_date) ) SELECT * FROM cte WHERE NOT EXISTS ( SELECT NULL FROM source_table WHERE cte.ShiftDate = source_table.ShiftDate ) Maximum range: 2047 days (~ 5.5 years).
R

What does IF...THEN look like in SQL SELECT?
Software Programming • sql server • • Raziyah00

2

0
Votes

2
Posts

0
Views

O

Operator CASE is the nearest analogue IF in SQL and maintained in all versions of SQL Server.SELECT CAST( CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END AS bit) as Saleable, * FROM Product The only time to use it is. CAST - if you need a buoy result; if you get a type intthe:SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END as Saleable, * FROM Product Operator CASE to another operator CASE and even include in the aggregate object.SQLer Denali (SQL Server 2012) adds to the operator http://msdn.microsoft.com/en-us/library/hh213574%28v=sql.110%29.aspx also present http://www.techonthenet.com/access/functions/advanced/iif.php : https://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select/6769805#6769805 )SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Selable, * from Product Translation of reply https://stackoverflow.com/a/63480/564240 https://stackoverflow.com/users/6819/ ♪
C

How to get mysql view definitions?
SQL, Database Testing • mysql view • • charlenef

2

0
Votes

2
Posts

0
Views

Show create view 'viewName' returns the SQL definition with create view statement while SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'VIEWNAME'; returns only definition. Both are correct for getting view definition.
A

Insert file set into 2 different tables
Software Programming • php mysql • • amarantha

2

0
Votes

2
Posts

0
Views

S

Brief multi-upload explanationTo work with uploading multiple files it is necessary to inform the name of input in format array:<input type="file" name="upload[]"> Note that the attribute value has keys at the end of the name [] this indicates that several values will be sent in that same field.On the server side you receive this data in the variable $_FILES with the key of the field name, in our case upload staying $_FILES['upload']. By default PHP distributes these files as follows:array(1) { ["upload"]=>array(2) { ["name"]=>array(2) { [0]=>string(9)"file0.txt" // Arquivo 1 [1]=>string(9)"file1.txt" // Arquivo 2 } ["type"]=>array(2) { [0]=>string(10)"text/plain" // Arquivo 1 [1]=>string(10)"text/html" // Arquivo 2 } } } Then to access them just pass the index of the desired file after the properties:$qtd = count($_FILES["fotos"]["name"]); for ($i = 0; $i < $qtd; $i++) { $nomeArquivo = $_FILES["fotos"]["name"][$i]; $tamanhoArquivo = $_FILES["fotos"]["size"][$i]; $nomeTemporario = $_FILES["fotos"]["tmp_name"][$i]; } Note that we are passing the index through the variable $i, so we can access every file we send to the server.Now let's take care of the inserts.As we know we can access the files by passing indices, we also know that the first index of a array It is 0 (saved) arrays associative or disordered).Store in a variable the amount of files sent:$qtd = count($_FILES["fotos"]["name"]); Check if any file was sent and if it is, store the first in the product tableif ($qtd > 0){ // acessa o primeiro arquivo com o índice 0 $nomeArquivo = $_FILES["fotos"]["name"][ 0 ]; $tamanhoArquivo = $_FILES["fotos"]["size"][ 0 ]; $nomeTemporario = $_FILES["fotos"]["tmp_name"][ 0 ]; // Faça o upload do arquivo $arquivoArray= explode(".", $nomeArquivo); $extensao = end($arquivoArray); $arquivo = $caminho.md5(time().rand(3212, 12043)).'.'.$extensao; if(move_uploaded_file($nomeTemporario, $arquivo)){ $sqlInsere = $database::query("INSERT INTO produtos (nome,descricao,categoria,destaque, sub_categoria, imagem) VALUES ('".$produto."', '".$informacao."','".$categoria."','".$destaque."', '".$subCategoria."', '".$arquivo."')"); $last = $database->lastInsertId(); // Pega o ID do último INSERT } else { echo "Não foi possível registrar o produto, falha no upload da imagem"; exit; } } Then check if more than one file was sent and if the variable with the registered product ID is not empty:if ($qtd > 1 && !empty($last)){ Then start registering the images from the second that in case is ID 1 // Inicia o loop apartir do indice 1, pois o indice 0 já foi registrado for ($i = 1; $i < $qtd; $i++) { $nomeArquivo = $_FILES["fotos"]["name"][$i]; $tamanhoArquivo = $_FILES["fotos"]["size"][$i]; $nomeTemporario = $_FILES["fotos"]["tmp_name"][$i]; // faça upload do arquivo // registre no banco } } The whole code is like this:<?php if(isset($_POST['submitProduto'])){ //Caminho para salvar $caminho = "uploads/"; $produto = trim($_POST["produto"]); $informacao = trim($_POST["informacao"]); $categoria = trim($_POST['categoria']); $subCategoria = $_POST['subCategoria']; // Verifica Checkbox if (isset($_POST['destaque'])) { $destaque = 1; } else { $destaque = 0; } $qtd = count($_FILES["fotos"]["name"]); $last = false; $alert = 'danger'; // Verifica se algum arquivo foi enviado if ($qtd > 0){ // acessa o primeiro arquivo com o índice 0 $nomeArquivo = $_FILES["fotos"]["name"][ 0 ]; $tamanhoArquivo = $_FILES["fotos"]["size"][ 0 ]; $nomeTemporario = $_FILES["fotos"]["tmp_name"][ 0 ]; // Faça o upload do arquivo $arquivoArray= explode(".", $nomeArquivo); $extensao = end($arquivoArray); $arquivo = $caminho.md5(time().rand(3212, 12043)).'.'.$extensao; if(move_uploaded_file($nomeTemporario, $arquivo)){ $sqlInsere = $database::query("INSERT INTO produtos (nome,descricao,categoria,destaque, sub_categoria, imagem) VALUES ('".$produto."', '".$informacao."','".$categoria."','".$destaque."', '".$subCategoria."', '".$arquivo."')"); if ($sqlInsere){ $msg = "Produtos cadastrados com sucesso!"; $alert = 'success'; $last = $database->lastInsertId(); // Pega o ID do último INSERT } else $msg = "Não foi possível cadastrar os produtos." } else { $msg = "Não foi possível registrar o produto, falha no upload da imagem"; } } else $msg = 'Envie alguma imagem para continuar.'; // Se foi enviado mais de 1 arquivo e a variavel do ID do produto não está vazia if ($qtd > 1 && !empty($last)){ // Inicia o loop apartir do indice 1, pois o indice 0 já foi registrado for ($i = 1; $i < $qtd; $i++) { $nomeArquivo = $_FILES["fotos"]["name"][$i]; $tamanhoArquivo = $_FILES["fotos"]["size"][$i]; $nomeTemporario = $_FILES["fotos"]["tmp_name"][$i]; if (!empty($nomeArquivo)) { $arquivoArray= explode(".", $nomeArquivo); $extensao = end($arquivoArray); $arquivo = $caminho.md5(time().rand(3212, 12043)).'.'.$extensao; if(move_uploaded_file($nomeTemporario, $arquivo)){ $database::query("INSERT INTO produtos_fotos (id_produto, imagem) VALUES ('".$last."', '".$arquivo."')"); } else { echo "Não foi possível enviar a imagem"; } } } } $message = "<div class=\"alert alert-{$alert} text-center\">{$msg}</div>"; } ?>
M

Stock quantity Trigger
SQL, Database Testing • mysql mysql 5.7 • • magpie

2

0
Votes

2
Posts

0
Views

C

Taking your code, DELIMITER // CREATE TRIGGER UpdateStock AFTER INSERT ON ORDERS.Quantity FOR EACH ROW BEGIN UPDATE PRODUCT.Stocklevel SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity WHERE ORDERS.Quantity = NEW.Quantity; END; // DELIMITER; , I can hardly believe that you've read and understood any documentation -- not on triggers and not on updates at least. And it surely does not compile, let alone run even with errors... (And by the way, why not posting the relevant code here but instead on an other platform? Makes things unnecessarily harder, I think.) The ON clause of a trigger takes a table, not a column. So change ON ORDERS.Quantity to ON ORDERS. After UPDATE follows a table, not a column. So change UPDATE PRODUCT.Stocklevel to UPDATE PRODUCT. Via the the NEW pseudo table the values of the inserted row can be accessed in a FOR EACH ROW trigger. But not any other table or their columns or the table on which the trigger is executed, for that matter. So New.PRODUCT.StockLevel and New.ORDERS.Quantity are nonsense. Change SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity to SET StockLevel = StockLevel - New.Quantity. And finally, there is no column Quantity in your table ORDERS. So WHERE ORDERS.Quantity = NEW.Quantity; is just wrong either. And even if there were such a column, it would mean the stock level was set at all products having the same quantity (whatever that should mean in products) as the row inserted into order. I guess you want to update the stock level of the product in the order, so use the product id: WHERE ProductID = NEW.ProductID; That should do the trick.
K

Delete redudant values of resultset
SQL, Database Testing • mysql duplication • • kalyn

2

0
Votes

2
Posts

1
Views

R

One way of achieving it is using ROW_NUMBER() function in mysql if you are using mysql 8.0 above. SELECT CASE WHEN row_num = 1 THEN abbr_Company ELSE '' END companyname1, CASE WHEN row_num = 1 THEN name_Company ELSE '' END companyname2, ddate, turnover, name_Employee, idCodeTurnover, row_num FROM ( SELECT abbr_Company, name_Company, ddate, turnover, name_Employee, idCodeTurnover, ROW_NUMBER() OVER (PARTITION BY abbr_Company, name_Company ORDER BY turnover DESC) AS row_num FROM Profit INNER JOIN Employee ON Employee.idEmployee = Profit.idEmployee INNER JOIN Company ON Profit.idCompany = Company.idCompany ) X ORDER BY abbr_Company, name_Company, row_num; Here is a complete repro in dbfiddle Note as you haven't given how to define the duplicate, i have added abbr_Company,name_Company as PARTITION BY clause and in the order of highest turnover
Select Group with PHP
Software Programming • php mysql sql bootstrap 3 • • Laycee

2

0
Votes

2
Posts

0
Views

Good morning @Isadora AlmeidaI put here the way you did and I really couldn't fill the combo. Unfortunately I don't know why your code doesn't work. So you did I didn't know... ai did the way I usually do <body> <div class="form-group"> <select class="" name="agencia" tabindex="-1" style="height: 30px;" required="" id="alimentos"> <?php $resultado = mysql_query("SELECT estado_agencia FROM agencia group by estado_agencia"); echo(' <option value="">Selecione sua Agência...</option>'); echo('<optgroup>'); while ($dados = mysql_fetch_array($resultado)) { $estado = $dados['estado_agencia']; echo("<optgroup label=$estado> "); $resultado2 = mysql_query("SELECT * FROM agencia where (estado_agencia = '". $estado."')"); while ($dados2 = mysql_fetch_array($resultado2)) { $id_agencia = $dados2['id_agencia']; $descricao_agencia = $dados2['descricao_agencia']; echo ("<option value=$id_agencia>$descricao_agencia</option>"); } echo '</optgroup>'; } ?> </select> </div> </body> Edited with 2 sqls.It worked cool... He filled the combo correctly.I hope you helped in some way
K

How to ask SQL for a monthly sample (type field timestamp)
Software Programming • php sql request data streams • • Kadyn

2

0
Votes

2
Posts

0
Views

R

use https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format function mysql continue with this request, for a month and a year or a month, if the year is not important (then per centY need to be removed)select * from minicms where date_format(logs_time, "%m.%Y") = "07.2017"
J

invalid db driver codeigniter
SQL, Database Testing • mysql • • jonetta

2

0
Votes

2
Posts

0
Views

T

Rather inexplicably, a codeigniter upgrade from 3.0.0 to 3.0.1 solved this!
B

SQL query with subquery works in SQL SERVER 2016, but it does not work in SQL CE
Software Programming • sql server linq entity framework 6 • • Burma

2

0
Votes

2
Posts

0
Views

SQL query with SUBQUERY to LINQ running with SQL SERVER and COMPACT. The method DefaultIfEmpty() I can create the LEFT JOIN. Share for suggestions or recommendations.var query = from oc in db.Ocs join sb in db.SBultos on oc.Id equals sb.Oc_Id into ocsb from ocsb0 in ocsb.DefaultIfEmpty() where oc.Rut == ValorRut && oc.Orden == ValorOrden && oc.Carga == ValorCarga && oc.CPallet == 0 join ca in db.Canales on oc.CSucursal equals ca.CSucursal into ocsb0ca from ocsb0ca0 in ocsb0ca.DefaultIfEmpty() select new { CSucursal = oc.CSucursal, Correlativo = oc.SBulto ? ocsb0.Correlativo : oc.Correlativo, EPedido = oc.SBulto ? ocsb0.Unidad : oc.EPedido, CCanal = ocsb0ca0.CCanal } into FinQuery group FinQuery by new { FinQuery.CSucursal, FinQuery.Correlativo, FinQuery.CCanal, FinQuery.EPedido } into FinGroup select new { Csucursal = FinGroup.Key.CSucursal, Correlativo = FinGroup.Key.Correlativo, EPedido = FinGroup.Sum(x=>x.EPedido), CCanal = FinGroup.Key.CCanal }
T

How to group different results from different tables one table and in one row?
Software Programming • sql server sql • • tahishae

2

0
Votes

2
Posts

0
Views

R

This construction select sum(isnull(cantidad,0)) where c.tipo = 1 without a from It shouldn't work, but anyway, I understand what you're wanting is to make a conditional sum.select m.idinsumo, i.descripcion, sum(case when c.tipo = 1 then cantidad else 0 end) [total entradas], sum(case when c.tipo = 2 then cantidad * -1 else 0 end [total salidas], sum(case when c.tipo = 2 then cantidad * -1 else cantidad end) [total] from movsinv m left join conceptos c on c.idconcepto = m.idconcepto left join insumos i on i.idinsumo=m.idinsumo where c.tipo in (1, 2) and m.fecha between '01-01-2020 7:00:00' and '02-08-2021 23:59:59' group by m.idinsumo,i.descripcion order by m.idinsumo The structure is simple case when <condicion> then <valor1> else <valor2> endIn your case when tipo = 2 The idea is always to subtract cantidad
J

Function to calculate an average with date filter in mysql
Software Programming • mysql mysql workbenc languageh • • juvenalb

2

0
Votes

2
Posts

0
Views

As indicated in the comments, when using like in the clause where the query can take any value that contains the number you've passed to him. Instead, you should use where id_curso = 1SELECT SUM(car1) FROM Evaluacion WHERE id_curso = 1 AND fecha = 2020-01-10
J

How do you speed up the SQL sample?
Software Programming • mysql optimization sampling • • jonetta

2

0
Votes

2
Posts

0
Views

K

faster than NOT IN (1,2,3,4,...) it won't work. Even millions of records will be quickly sampled if you have id or UNIQUE or PRIMARY.
SQL Server does not connect in SQL Management
Software Programming • sql server sql • • morde

2

0
Votes

2
Posts

0
Views

Hi. Make sure that in the windows vc services you find SQL as in the image below and if you see if the same is as running. If you are not likely to find you did not install the sql server.

sql LIKE operator for sampling

Suggested Topics

Request for SQL as to add sample results to the third Software Programming • sql server sql • • Nykeriab

How to make a sql request for a sample of no data originally available (T-SQL) Software Programming • sql server sql • • jules

What does IF...THEN look like in SQL SELECT? Software Programming • sql server • • Raziyah00

How to get mysql view definitions? SQL, Database Testing • mysql view • • charlenef

Insert file set into 2 different tables Software Programming • php mysql • • amarantha

Stock quantity Trigger SQL, Database Testing • mysql mysql 5.7 • • magpie

Delete redudant values of resultset SQL, Database Testing • mysql duplication • • kalyn

Select Group with PHP Software Programming • php mysql sql bootstrap 3 • • Laycee

How to ask SQL for a monthly sample (type field timestamp) Software Programming • php sql request data streams • • Kadyn

invalid db driver codeigniter SQL, Database Testing • mysql • • jonetta

SQL query with subquery works in SQL SERVER 2016, but it does not work in SQL CE Software Programming • sql server linq entity framework 6 • • Burma

How to group different results from different tables one table and in one row? Software Programming • sql server sql • • tahishae

Function to calculate an average with date filter in mysql Software Programming • mysql mysql workbenc languageh • • juvenalb

How do you speed up the SQL sample? Software Programming • mysql optimization sampling • • jonetta

SQL Server does not connect in SQL Management Software Programming • sql server sql • • morde

Request for SQL as to add sample results to the third
Software Programming • sql server sql • • Nykeriab

How to make a sql request for a sample of no data originally available (T-SQL)
Software Programming • sql server sql • • jules

What does IF...THEN look like in SQL SELECT?
Software Programming • sql server • • Raziyah00

How to get mysql view definitions?
SQL, Database Testing • mysql view • • charlenef

Insert file set into 2 different tables
Software Programming • php mysql • • amarantha

Stock quantity Trigger
SQL, Database Testing • mysql mysql 5.7 • • magpie

Delete redudant values of resultset
SQL, Database Testing • mysql duplication • • kalyn

Select Group with PHP
Software Programming • php mysql sql bootstrap 3 • • Laycee

How to ask SQL for a monthly sample (type field timestamp)
Software Programming • php sql request data streams • • Kadyn

invalid db driver codeigniter
SQL, Database Testing • mysql • • jonetta

SQL query with subquery works in SQL SERVER 2016, but it does not work in SQL CE
Software Programming • sql server linq entity framework 6 • • Burma

How to group different results from different tables one table and in one row?
Software Programming • sql server sql • • tahishae

Function to calculate an average with date filter in mysql
Software Programming • mysql mysql workbenc languageh • • juvenalb

How do you speed up the SQL sample?
Software Programming • mysql optimization sampling • • jonetta

SQL Server does not connect in SQL Management
Software Programming • sql server sql • • morde