Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [string-splitting]

Use for questions about spitting strings using a specified delimiter.

35 questions
8
votes
2 answers

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 string and a 9-digit-substring using SQL Server,…
Werner Aumayr
  • 181
  • 4
6
votes
1 answer

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 contents look like this There are of course multiple…
Chessbrain
  • 741
  • 11
  • 18
5
votes
1 answer

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 But in this way this doesn't work, so I need in…
BlueMark
  • 237
  • 1
  • 3
  • 14
5
votes
1 answer

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…
user3050153
  • 67
  • 2
3
votes
3 answers

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 executed multiple times for the STRING_SPLIT…
raeldor
  • 143
  • 1
  • 5
2
votes
2 answers

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 in a column of ints somewhere. Because of the…
Christofer Ohlsson
  • 131
  • 6
2
votes
3 answers

SQL - Convert text to a list of numeric values

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,13,14 table1 has column ID (numeric). I would…
Stefan Colosimo
  • 29
  • 1
  • 3
2
votes
1 answer

Split a row into two for attendance seet

I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a Grid that was getting week-by-week data Pivoted. I've opted for a Scheduler control by DevExpress. A sign-in and a sign-out are tow be…
Hassan Gulzar
  • 145
  • 2
  • 7
1
vote
1 answer

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

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 ======================================= | ID | …
user275517
  • 111
  • 1
  • 2
1
vote
1 answer

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 split chunks (well before 64MB), At the end of…
Maxime Fouilleul
  • 3,255
  • 20
  • 21
1
vote
3 answers

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 string_split(@bk, '~') and I can also do it like…
James
  • 2,484
  • 4
  • 19
  • 44
1
vote
0 answers

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", "review_count" : 12, "categories" : "Restaurants,…
tom dinh
  • 79
  • 8
1
vote
1 answer

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 many methods, substring, find, splitstring; we need…
user172734
1
vote
0 answers

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 path_long. Example I have a column named path with content like…
klor
  • 155
  • 2
  • 11
1
vote
2 answers

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 unable to use function as I cannot create objects…
Bob Klimes
  • 2,720
  • 1
  • 14
  • 28
1
2 3