Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [substring]

The SUBSTRING function is used to return a portion of a string.

0
votes
1answer
29 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 ...
0
votes
0answers
21 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
vote
3answers
48 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 ...
0
votes
1answer
71 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 ...
0
votes
1answer
62 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 ...
0
votes
1answer
45 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 ...
0
votes
1answer
23 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 ...
0
votes
1answer
130 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
2answers
124 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 ...
1
vote
2answers
68 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 ...
0
votes
2answers
133 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 ...
0
votes
1answer
180 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 ...
4
votes
2answers
2k 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
vote
1answer
2k views

PostGres - 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 ...
0
votes
1answer
276 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 ...
2
votes
1answer
436 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'...
0
votes
2answers
57 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 ...
2
votes
1answer
2k 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 ...
1
vote
2answers
688 views

SUBSTR() function in IF statement [closed]

+----+---------------+-------+ | id | link | title | +----+---------------+-------+ | 1 | v_156399569 | abc | +----+---------------+-------+ | 2 | y_u0inSsAoFU4 | xyz | +----+---------...
1
vote
0answers
50 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) &...
2
votes
2answers
794 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 ...
2
votes
2answers
4k 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 " ". ...
1
vote
2answers
160 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 ...
14
votes
1answer
293 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 ...
-2
votes
1answer
145 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('@',...
3
votes
2answers
846 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 ...
0
votes
1answer
703 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(...
6
votes
3answers
1k views

Is there a SQL Server implementation of the Longest Common Substring problem?

Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but ...
4
votes
1answer
6k views

Why do string functions on SQL based database servers start at position 1 instead of 0? [closed]

This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the ...
0
votes
1answer
333 views

Substring a result and renaming it

I wrote the below query which works just fine. The PARAM_ID has a CLC_Q<number> form. SELECT * FROM DB.TABLE WHERE PARAM_ID LIKE 'CLC_Q%' AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45 ...
3
votes
2answers
15k views

How to separate name string by the spaces in the name in T-SQL

I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr I need to split those out into the four component parts of the name (if the name has four parts) to put each of them ...
1
vote
2answers
1k views

How to pull number code from strings buried in parentheses?

I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing... Data Samples: Widget (1234) Different Widget (123) Many ...
0
votes
1answer
457 views

SQL Query to compare (HL7) value at certain position in string based on delimiters (not position)

This is complicated by the fact that I don't know what DBMS the backend uses. I know it's Cache, but I don't seem to be able to do anything but the most basic commands. I cannot seem to SET or ...
1
vote
1answer
1k 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 ==========...
0
votes
2answers
13k views

Single query approach to split and copy string values in a column

This is a part of my previous question. I have the below logic to split a string value from a column in table tblProjects as below: SELECT @Docs = Documents FROM tblProjects WHERE ID = @ID SET @...
1
vote
1answer
679 views

Get all the data between Characters [closed]

I am trying to extract all the variable from the following string; string = 'factor1|factor2|factor3' This is just a test string. In real string, I do not know the number of factors. What I want is ...
0
votes
1answer
1k views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
1
vote
3answers
9k views

Extracting an an integer value from an xml column [closed]

I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will ...
4
votes
2answers
472 views

Is this substring_index behaviour documented or a bug?

mysql> show variables like 'version'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | version | 5.5.38-...