Questions tagged [string-splitting]

Use for questions about spitting strings using a specified delimiter.

Filter by
Sorted by
Tagged with
3 votes
2 answers
556 views

How to delay convert() evaluation until after join

The following query converts a single string of packed CSV that represents 13k rows by 2 columns. Column A is a bigint. Column B is a smallint. declare @dataCsv nvarchar(max) = '29653,36,19603,...
crokusek's user avatar
  • 2,120
0 votes
2 answers
1k views

Converting multiple comma-separated columns into rows

I have an SQL Server database table that holds comma separated values in many columns. For example: id Column B column c 1 a,b,c, 1,2,3, 2 d, ,f, 4,5,6, 3 g,h,i,j, 7, ,9,8, I want to split all ...
jawad riaz's user avatar
-3 votes
1 answer
694 views

I want to split the text in the row into separate columns

I have this column and rows I want to split the text in the row into separate columns like this.
Husnia Babaeva's user avatar
0 votes
3 answers
1k views

Extract a substring where the delimiter might appear more than once

I have a column of strings with this pattern <email> - <id>. Email is always the first string. I would like to extract only the email address but the problem here is that an email address ...
gabtzi's user avatar
  • 161
-2 votes
1 answer
1k views

split a row value according to a specific column

I have total value in table A and need to get row value in table B until reached value in table A. Table A ID Available 1 99 2 0 Table B ID Rack RequiredQty 1 A 60 1 B 102 1 C 8 2 A 10 ...
Alice's user avatar
  • 1
-4 votes
1 answer
220 views

Select INTs from start of string separated by a character (pipe). Update other columns using these INTs

In the above screen shot, the Description column has many special characters. We want before | number update in T1 column and | after number in T2 column. Please suggest the proper syntax. If there ...
karthik sanapala's user avatar
5 votes
1 answer
189 views

Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings

Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here). current_data 1,2,3 1,4 1,5,...
user3050153's user avatar
6 votes
1 answer
13k views

Split a comma-separated record into its own rows

I have the following table, named stores: store_id INT emails VARCHAR That contains values: store_id emails 1 [email protected],[email protected] 2 [email protected],[email protected] 4 admin@...
Dimitrios Desyllas's user avatar
0 votes
2 answers
203 views

Transform a varchar field with #-delimiters into multiple rows

I am working with a database where some data (integer values representing selected options of a dropdown menue) is saved as a string (varchar) inside only one table column. In products_table (let's ...
emma's user avatar
  • 101
1 vote
2 answers
3k views

Splitting a column on underscores [duplicate]

What SQL syntax would split this column into three new columns (split would be on the underscore)? There are other columns in the table, but I would like to split these into three (underscore based).
CalgaryCoolCat's user avatar
0 votes
1 answer
65 views

How to split field and run sub query using it?

Given two tables T1 and T2 below, is there a way in MySQL 5.7.x to get such a list field from T1, so that none of its entries are present in T2? T1.list is a regular VARCHAR, containing T2.vals ...
Ninius86's user avatar
  • 103
1 vote
3 answers
4k views

How did you split strings before string_split()

I have some strings I need to split on a SQL Server 2014 instance. I would normally do it something like this: declare @bk nvarchar(1000) set @bk = 'SIS5.0~person_id~696969' select value from ...
James's user avatar
  • 2,612
2 votes
2 answers
3k views

SQL Server - Select where using split - without declaring function

SQL Server. Compatibility level 120. I have a table where one VARCHAR column contains semicolon-separated integral values. I would like to SELECT against the values in this column as if they existed ...
Christofer Ohlsson's user avatar
0 votes
2 answers
64 views

Convert Text Column Web Service Data to Columns

I have a database table which stores web transactions for a fundraising donation page. One of the columns in the table stores a bunch of concatenated data in the format 'data1=value1 data2=value2 ...
Tyler Hahn's user avatar
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 ...
Neeti's user avatar
  • 1
1 vote
0 answers
2k views

splitting an string in an array into an array Mongodb

I have the current document: { "_id" : ObjectId("5c95b81b10ce2a953215c269"), "name" : "Œufs & Cie", "city" : "Brossard", "state" : "QC", "postal_code" : "J4Y 0G4", "...
tom dinh's user avatar
1 vote
1 answer
8k 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 ...
user avatar
8 votes
2 answers
2k views

Fastest way to split/store a long string for charindex function

I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function). I have tested this with a 1GB ...
Werner Aumayr's user avatar
6 votes
1 answer
4k views

How to split XML array into separate rows (while upholding consistency)

I am working on the database dump of this exact stack exchange section. While I am working on it I have encountered one issue that I am currently unable to solve. In the XML File Posts.xml the ...
Chessbrain's user avatar
  • 1,213
0 votes
1 answer
2k views

Index for split string. PostgreSQL 10

I have the following string in db: A/B/C/ A/B/D/ E/F/C/ It's the uri column. The table is the following: create table meta_info ( id bigserial primary key, uri varchar, created ...
user565447's user avatar
0 votes
1 answer
59 views

Database organization for mirroring / partitioning / multistore [closed]

I know that is kind of generic question, but there is not other way to find a good answer. I have a requirement to create a network of a server and few simple PCs. My boss wants to have the database ...
Nianios's user avatar
  • 113
1 vote
0 answers
1k views

MySQL: Loop on each column value & split column by delimiter & replace numbers with values from another column

I would like to loop on each value of path column & split content by '/' delimiter & replace numbers with values from another column using MySQL & store result into another new column ...
klor's user avatar
  • 157
0 votes
1 answer
4k views

MySQL: Split column by delimiter & replace numbers with values from another column

Using MySQL, split column by delimiter & replace numbers with values from another column. I have a column named 'path' having paths like '/426/427/428'. I would like to replace category numbers ...
klor's user avatar
  • 157
0 votes
1 answer
2k views

why my split string functions are not working with large OBJECT_DEFINITION?

I have in one of my databases a stored procedure called PROCEDURE [repl].[usp_upd_repl_out_application], that is not exactly a very clever stored procedure but it has over 389 lines. As I need to ...
Marcello Miorelli's user avatar
0 votes
1 answer
66 views

Problems splitting and aggregating data in SQL

Hello I have the following data in csv file: OrderID, OrderType, Amount, Price, Time 1, Buy, 10, 1, 10/01/2018 10:30 AM 2, Buy, 2, 1, 10/01/2018 11:30 AM ...
Ivan Ivanov's user avatar
-2 votes
2 answers
810 views

Error when using a query as a function parameter [closed]

I have followed this tutorial for creating a string splitting function. When I try to run the function from the article using a SELECT as the first parameter, I get the following error: select '...
hello B's user avatar
  • 97
1 vote
2 answers
4k views

Split two delimited strings in same order without function

I am trying to split two columns with delimited strings into rows. The positions of the values in each string are related so I am trying to split it out so that the related values are in a row. I am ...
Bob Klimes's user avatar
  • 3,234
1 vote
1 answer
37 views

Would db corruption be further minimized if I remove querying on a backend table from the user's front end?

I have an MS Access database that was being used by 30 people. They all use it at different times of the day, but within any 8 hour block of time there might be 10 people using it. This database was ...
TheMortiestMorty's user avatar
0 votes
1 answer
1k views

Querying an array of strings with a string that represents search terms

I have an array of values like: array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most and some text like: 'foo hello-world hello1world' and I would like to get ...
Randomize's user avatar
  • 1,183
2 votes
3 answers
10k views

SQL - Convert text to a list of numeric values [duplicate]

Looking for SQL code to transform the text string '12,13,14' to this list of numbers +----+ | 12 | +----+ | 13 | +----+ | 14 | +----+ Example: table0.params (varchar) field contains this text: 12,...
Stefan Colosimo's user avatar
-4 votes
1 answer
3k views

Split hyphenated nvarchar value [duplicate]

I have a table with 6 columns one of which is a NVARCHAR (65) datatype. All of the entries in this table have the format XXXXX-nnnnnn. I want to be able to use the '-' as a delimiter and construct a ...
zapcon's user avatar
  • 73
3 votes
3 answers
2k views

Performance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))

I seem to be having a huge performance gap between using hard coded values for a SELECT IN, vs a STRING_SPLIT. The query plans are identical except for the last phase where the index seek is getting ...
raeldor's user avatar
  • 143
0 votes
2 answers
62 views

MS SQL 2012 - Need to split records from 4 different fields then query those

So I have a table called 'WORK_ORDER' and the ID is a unique text example '16-0833' next would be 0834, 0835, etc. In that table there are 4 user fields.. USER_1, USER_2, USER_3, USER_4.. This top ...
Mark's user avatar
  • 23
1 vote
1 answer
6k views

Splitting a table into two tables randomly with 50-50 percent of records in impala

I have a table having n number of records in impala. I need to find out how can I divide that table into two equal halves with 50 - 50 percent of records in each.
user97537's user avatar
1 vote
2 answers
2k views

Split String Function

I have a question regarding this Split String function Aaron Bertrand recommends here. With it you can extract information between two slashes. Now if I want to extract not only one piece of info ...
Jack Ryan's user avatar
1 vote
1 answer
3k 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 ==========...
user275517's user avatar
0 votes
2 answers
8k views

Remove a character from a records enclosed between double quotes

I have a table in which every row have data like: 0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014 What I want is to remove every comma (,) that is enclosed between double quotes " ...
Deep Sharma's user avatar
1 vote
1 answer
1k views

Why MongoDB starts to split before 64MB

I'm testing the MongoDB Sharding with a Sharded Collection and a forloop to insert arround 1M documents to see how splitting and moving works. I'm surprise that after few documents MongoDB starts to ...
Maxime Fouilleul's user avatar
5 votes
1 answer
30k views

Split/explode comma delimited string field into SQL query

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216' and I want to use it to search IN this field: SELECT * FROM table1 WHERE id IN (id_list) id is integer id_list is varchar/text ...
BlueMark's user avatar
  • 247