All Questions
16 questions
1
vote
0
answers
66
views
How does mysql_real_escape_string() work?
I have to send some string (in C) as a query to MySQL, so i used mysql_real_escape_string() to escape some characters like \0 or \n:
#include <mysql/mysql.h>
int loginQuery(char *Nickname, char *...
- 11
0
votes
2
answers
971
views
Indexing on a fixed-length string (10 characters - phone numbers) for a large number of entries (performance)?
I understand that a lot of similar questions have already been asked but I could not find a definitive answer for my question.
Briefly,
I have to authenticate users using a REST API, with their phone ...
- 9
0
votes
1
answer
562
views
What exactly is the datatype input into a DATETIME value in MySQL
I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right ...
- 105
1
vote
1
answer
3k
views
How to write correct array string parameter for mysql query?
I'm trying to put up a correct string to pass as a parameter to a simple query.
Correct query that returns records:
SELECT id_s FROM group_of_id_s
WHERE date IN ('2020-08-01','2020-08-01','2020-08-02',...
- 77
2
votes
1
answer
45
views
Performing SQL string manipulations on a single column
I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals.
For example:
Input table:
...
- 27
1
vote
2
answers
981
views
SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause
I have a MySQL query:
SELECT
p.informationProvider as pGLN,
p.productID,
p.productStatus
FROM tblproducts AS p
WHERE
-- informationProvider is of type varchar(14)
p....
- 452
1
vote
1
answer
848
views
Does a Levenshtein distance involve some computation for each single row?
Let's say I have, in a SQL DB, a table with 2 columns: id integer, description text and 100,000 rows. The column description has always < 20 characters.
If I want to:
SELECT * FROM mytable WHERE ...
- 171
13
votes
4
answers
69k
views
Find if any of the rows partially match a string
I want to see if a table contains any sub-string of a given string.
Let's say I have a string
somedomain.com
In database I have:
blabladomain.com
testdomain.com
domain.com
I need to make a query ...
- 263
3
votes
2
answers
3k
views
Remove substring from one column and put it on other column
I have a table like this:
CREATE TABLE `mytable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`column2` varchar(255) COLLATE ...
- 185
13
votes
2
answers
13k
views
Likes or votes for posts
I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would ...
- 247
0
votes
1
answer
4k
views
MySQL trigger if new value is not equal to varchar
StackExchange, I'm trying to create a trigger in MySQL (before insert) that gives an error if the new value of "sifra" is not S, P, K, A or Z, but it I'm getting an error even if the said criteria is ...
- 5
1
vote
1
answer
1k
views
MySQL distinct string
I have the following sample table.
drop table if exists fruit;
create table fruit
(
id int not null auto_increment primary key,
tag varchar(100) not null
);
insert into fruit (tag) ...
- 111
4
votes
1
answer
4k
views
Using an index for both asc and desc on a string column
I know a trick to make order by queries faster when we want to order by asc and desc.
For integers store a negated value.
So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, -2,...
- 1,013
2
votes
1
answer
280
views
MYSQL testing string field contains another altered string field
I have this table :
mysql> desc mytab;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----...
- 141
0
votes
1
answer
8k
views
Split values from field and count them according to another field
I have following MySQL db structure:
content
=======
id | tags
---------
1 | ,abc,def,ghi,
2 | ,abc,foo,
3 | NULL
4 | ,foo,bar,
categories
==========
id
--
1
2
categories_to_content
==============...
- 1