All Questions
16 questions
0
votes
2
answers
113
views
Using a WHERE colname NOT LIKE (select * from otherTable)
So in essence what im trying to do is this:
Given TBL1 (around 8,000 rows)
letters
abababa
bcbcbcb
cdcdcdc
and TBL2 (around 2,000 rows)
letters
ab
bc
I want to write a query to find every row ...
3
votes
1
answer
318
views
String Manipulation of the Result from Recursive CTE
Good afternoon everyone
I found just one post here within the last year about this, but it doesn't help my situation.
I have been working with MySQL and trying to improve my knowledge of recursive CTE....
- 33
1
vote
1
answer
58
views
How to extract certificate attributes from a single column and split into many?
In my table I've a field (let say cert_attr) which stores Certificate X.509 Attributes.
Here is the example of 3 rows (each line corresponds to a field):
"CN=User1, OU=Eng, O=Company Ltd, L=D4, S=...
- 475
1
vote
1
answer
268
views
Mass update all cells in a column with a substring of it's original value
I need to trim the last seven characters off of all the cells in a column.
This query returns the results that I need
SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as ...
- 13
0
votes
0
answers
101
views
Speed up query on large MySQL table
SELECT property, geo, stars
FROM table
WHERE SUBSTRING_INDEX(geo, '>', 3) LIKE '%$mylocation%'
...loop to show results
geo column has values like - Spain>Canary Islands>Lanzarote>Some ...
1
vote
1
answer
89
views
Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text
I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this:
Some code...
<div> </div>
<div>[flv:http://submarino-libre....
- 27
0
votes
0
answers
103
views
How performant is finding a substring in database?
To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item.
For example:
I have a request to /animals/dog/darktheme/. The ...
- 111
0
votes
0
answers
191
views
problem with substring in mysql
I have simple EAV like database which has 3 tables: products, attributes, attribute_values.
I am using below code to find all products that have attribute 'length' and then I am using substring ...
- 1
2
votes
1
answer
6k
views
MySQL "Out of sort memory" error on a simple GROUP_CONCAT query
I have a (what it seems to me) simple query which fails to run on a simple table, the error is:
Out of sort memory, consider increasing server sort buffer size
These are the SQL statements which I'...
- 121
1
vote
2
answers
4k
views
SUBSTR() function in IF statement [closed]
+----+---------------+-------+
| id | link | title |
+----+---------------+-------+
| 1 | v_156399569 | abc |
+----+---------------+-------+
| 2 | y_u0inSsAoFU4 | xyz |
+----+---------...
- 113
1
vote
0
answers
62
views
Finding and replacing non-specific text to lowercase variant
I've been cracking my brain on a problem that I had at work. In one of my tables is a bunch of rows, with a certain column that contains XML data that has several properties like this: (simplified)
&...
- 111
1
vote
2
answers
299
views
Get the remainder of SUBSTRING_INDEX
I have a problem regarding SUBSTRING_INDEX.
As the image shows, I can get the city by
SELECT SUBSTRING_INDEX(location,'-',1)) FROM my_table
However for the state_country column, I need the ...
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
0
votes
1
answer
2k
views
mysql select everything before a number
I am trying to get the outer and prefix for uk postcodes i.e. NW3 6BX. A space divides the outer part of the post code for which extracting it can be easily achieved by doing:
SUBSTRING_INDEX(...
- 143
1
vote
1
answer
4k
views
How to split a row based on a delimiter into multiple rows and retain the ID and ordering by using a single SQL statement for MySQL [duplicate]
Scenario:
A MySQL table
A Delimiter (e.g. comma) is used to separate entities inside a string store in a column of the MySQL table
MySQL table contain 2 columns - ID and Countries_Involved
==========...
- 111
4
votes
2
answers
1k
views
Is this substring_index behaviour documented or a bug?
mysql> show variables like 'version';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| version | 5.5.38-...
- 40.2k