Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [string-splitting]

Use for questions about spitting strings using a specified delimiter.

42 questions
25
votes
1 answer

STRING_SPLIT with order not working on SQL Server 2022

I need to use STRING_SPLIT with enabled ordering: We have upgraded all of our servers to SQL Server 2022 and change the compatibility level to 160, but the following code is not working: SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', '…
gotqn
  • 4,348
  • 11
  • 52
  • 91
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
  • 5
7
votes
1 answer

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 user_1@example.com,user2@example.com 2 uswe3@example.com,user4@example.com 4 admin@example.com And I want to generate the…
Dimitrios Desyllas
  • 873
  • 2
  • 14
  • 30
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
  • 1,223
  • 3
  • 17
  • 25
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
  • 247
  • 1
  • 3
  • 15
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
1 answer

Temp tables streamline query

I am trying to get to the Customer table from data in the SiteConfiguration table. I am getting the data and setting it to @AdditionalVoteViewers, which would be a string of emails address. I then split the string and insert the results into a temp…
Jefferson
  • 111
  • 1
  • 7
3
votes
2 answers

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,36,19604,36,29654,36'; -- abbreviated, actually…
crokusek
  • 2,110
  • 4
  • 25
  • 34
3
votes
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,668
  • 5
  • 28
  • 51
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
  • 6
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
  • 1
  • 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
  • 1
  • 3
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,565
  • 25
  • 21
1
vote
2 answers

Splitting a column on underscores

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
  • 41
  • 1
  • 5
1
2 3