Questions tagged [substring]
The SUBSTRING function is used to return a portion of a string.
63 questions
0
votes
2
answers
112
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
2
votes
1
answer
314
views
Microsoft SQL Server - Get first n alphanumeric characters with variable length, meeting ABCD-1234 or ABCD-1234567 format
My MS SQL 2019 server version is 15.0.2080.9 (X64)
I have a table like
id
message
2003
ABCD-1234: some text
2897
ABCD-5678
2456
ABCD-675456: some text
3204
ABCD-4512345 :some text
4567
ABCD-2345
...
5
votes
2
answers
1k
views
At finding a substring, find the end position as well
A LIKE clause can test if a string occurs in another and the CHARINDEX function can give the start position of the first match.
In my case, I'm interested in the end position though, which is, due to ...
- 775
0
votes
3
answers
383
views
Finding duplicate letters in a string beginning and ending at certain positions in MariaDB?
I need to find all strings containing double letters in positions between 4th and 10th inclusive.
I can find all double letters by
'([a-zA-Z])\1{1,3}';
and positions by
SELECT SUBSTRING(columnmame, ...
- 103
0
votes
2
answers
2k
views
Extract/parse info between delimiters
I have a column where there are 10 "fields" separated by the pipe character, looks like this:
texthere | somemoretext | sometinghere | moreinfo | evenmorehere | etc |
etc |
I've been ...
2
votes
2
answers
503
views
Return part of a string with variable length
I have data in a table column like below:
host=0.0.0.0 port=5432 dbname=database_name user=pglogicaluser
host=0.0.0.0 port=5432 dbname=database_name2 user=pglogicaluser
I want to write a query to get ...
- 532
4
votes
2
answers
877
views
SQL join on an edited field
I'm trying to join Table1 and Table2 on the Url fields. However all the Urls in Table2 end with a "/" as per the example below:
Table1
╔═════════════════════╗
║ Url1 ║
╠═══════...
- 43
-1
votes
1
answer
367
views
Strange behavior with Quotename and substring [closed]
I am seeing some behavior that I cannot figure out. We have a sproc that joins a bunch of tables and filters down some pipe-separated (|) values in a column. We cross apply a string splitter (I ...
- 115
2
votes
1
answer
2k
views
Replacing a specific string in oracle
I've a database value 'INS. Company Cancelled' where multiple values are separated by '.'
I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled'
I've tried below ...
- 193
1
vote
2
answers
2k
views
Mssql subquery inside a substring
I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.
The subselect collects all rows and converts them ...
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
0
votes
1
answer
69
views
SQL Server 2008 - Replacing csv substrings in view-row using data from other table
Using SQL server 2008, is there a way to perform SELECT query in a view replaces a row containing comma-separated values with their corresponding text value from another table? STRING_SPLIT and ...
- 3
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
-1
votes
1
answer
196
views
Concat digits into xxx-xxx form
Example, I get data from eclipse and insert into 'abc' table.
so, now how I can get the data from the 'abc' table and concat the digit?
Example: I enter 123456 then in SQL need to concat into format ...
- 1
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
3k
views
I am trying to get the string between hyphen (-) which is before (+) upto next colon (:)
03/02/2020, 7:41 pm - +91 9999999999: At 7:10;
03/02/2020, 7:41 pm - + abcd : At 7:10;
Note: The string in between - and : is not of same length in each record. Please, help me with a solution ...
- 13
0
votes
2
answers
20
views
Combining previously different rows after changing value during query
I'm currently running a query that is supposed to return a salesperson's aggregated revenue, grouped by individual clients.
Because of how user input was handled, there are a lot of near-duplicate ...
- 1
1
vote
1
answer
3k
views
Postgres text column too large for an index - searching for IP address, so I don't think text search will work
I have a Postgres database with a column that has a large amount of text in it. There are potentially multiple IP addresses in the text, as well as a lot of other text.
I need to do a query
WHERE ...
- 121
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
5
votes
1
answer
766
views
MSSQL: Generate a 8 digit serial/Barcode number from a prefix number
I have a complicated task ahead of me and I really could use some of your brains to help me out.
At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix ...
- 177
0
votes
3
answers
2k
views
Sum of decimal values that come in a string
Is there an easy way to get the sum of all decimal values in a string column.
Example:
"ABC $3.25, DEF $2.50, HIJ2 $0.25"
"ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50"
I want to add only the values ...
- 1
1
vote
1
answer
2k
views
Using SUBSTRING and RTRIM Together
I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into ...
- 189
0
votes
1
answer
3k
views
how to use substring with a delimiter
Employee_M
City Emp_Num_ID
Cleveland 2164445437/FTTD/JAMES, TYLER/139561151
Tokyo 1261120379/FTTD/BOYD, ADAMS/14468140
Marakech 4049838896/FTTD/SMITH, TULY E/13956144
Houston ...
- 15
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
3
votes
3
answers
4k
views
Is there a more concise way in Postgres to extract a portion of a string?
I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I ...
- 733
1
vote
2
answers
5k
views
SQL Split Row Data Separated by Spaces
I am looking for a query
to find nth value in a list. The separator is anything greater than or equal to 2 spaces. (it can be 3, or 5 spaces).
Trying to avoid scalar value functions, since ...
user172334
1
vote
1
answer
9k
views
SQL Split Address Line into Separate Columns
Whats the best way to split a comma list column into different columns? I am looking for a simple algorithm, heard it is best to avoid scalar value functions, as they might be slow. Searching into ...
user172734
0
votes
1
answer
998
views
Select All Instances of Substring in a String
I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId ...
- 155
0
votes
1
answer
82
views
Query between tables to delete similar records from another table
I am writing an SQL query on two tables products01 and products02 in a PostgreSQL database.
For the entire set of records in products02 take the substring (first 10 characters of volumeId and use ...
- 13
0
votes
1
answer
2k
views
Extract part of a string in Postgres
I have a question how to extract a substring.
Here an example string:
{{from,005021889988,,Amt},{setup-from,66,,},{alert-to,66,ConTeach Zentrale,ConTeach Zentrale},{conn-to,66,ConTeach Zentrale,...
3
votes
2
answers
5k
views
Trying to check if a string contains a number exclusively
I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string ...
- 629
1
vote
2
answers
497
views
SUBSTRING and IF statement
I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9.
Some of them are digits with (-), other are digits. I want ...
- 37
0
votes
2
answers
356
views
Sql Substring and Search String
I have a table in my database for storing supplier contacts and it is laid out like so:
Table: supplier_contacts
contact_id INT IDENTITY(1,1)
first_name VARCHAR(50) NOT NULL,
last_name ...
- 391
0
votes
1
answer
2k
views
Timestamp and substring with regular expression
I am using postgresql and I have the following table
Table "public.test"
Column | Type | Modifiers
--------+--------------------------+-----------
name | text ...
- 13
5
votes
2
answers
26k
views
Substring without the first n characters
I'm developing a SQL Server 2016 stored procedure and I want to get the last characters of a varchar(38) column.
I know there will always be at least 18 characters and I don't know the exact length ...
- 1,873
2
votes
1
answer
10k
views
How to Extract SubString between two positions
I have a text column (emailbody) in a table (notification) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains ...
- 391
0
votes
1
answer
487
views
Oracle procedure with input parameter that performs multiple SUBSTR
I have a table Employee with the columns employee_id as type number, and employee_name as type varchar.
I need a procedure that takes an input string to insert employees into the table. The ...
- 21
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
0
votes
2
answers
1k
views
How to extract the date from this varchar column
I need to remove records from this table that are older than 30 days.
We only have 2 columns in this table. The first date after 'Printed_%' is what I will be using to determine if the record should ...
- 1,133
4
votes
2
answers
14k
views
Extract filename without extension from the absolute location
I'm trying to get a filename contained in the value of a specific column of my table. My table looks like this:
absolutel_path
\\Path\filename.extension
I need to extract the filename (in the above ...
- 63
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
2
votes
2
answers
2k
views
How to simplify repeating variables in where clause for multiple columns SQL Server
I know there has to be a better way to evaluate a set of repeating variables against multiple columns. I've been searching for a while and still don't have a great solution. Is there a better way to ...
- 23
6
votes
2
answers
21k
views
How do you count the occurrences of an anchored string using PostgreSQL?
If I have a string in a column on a row in a table like this
1 2 2 2 2 2 2
How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ".
...
- 64.6k
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 ...
13
votes
1
answer
413
views
Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016?
Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions?
The reason I'm asking is that ...
- 4,268
-2
votes
1
answer
399
views
Delete everything after 3 character
Anybody knows how to delete everything after third '#' ?
I tried it like this but it's not working:
declare @s varchar(200) = 'abcd#efg#hij#delete';
select left(@S, charindex('@', @S, charindex('@',...
- 7
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