Questions tagged [string-splitting]
Use for questions about spitting strings using a specified delimiter.
41 questions
25
votes
1
answer
1k
views
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 * ...
- 4,318
3
votes
1
answer
135
views
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 ...
- 79
3
votes
2
answers
570
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,...
- 2,130
0
votes
2
answers
2k
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 ...
-3
votes
1
answer
848
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.
0
votes
3
answers
2k
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 ...
- 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
...
- 1
-4
votes
1
answer
241
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 ...
5
votes
1
answer
203
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,...
- 67
6
votes
1
answer
17k
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@...
0
votes
2
answers
252
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 ...
- 101
1
vote
2
answers
4k
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).
0
votes
1
answer
107
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 ...
- 103
2
votes
3
answers
5k
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 ...
- 2,650
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 ...
0
votes
2
answers
79
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 ...
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
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",
"...
- 79
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
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 ...
- 181
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 ...
- 1,223
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 ...
- 115
0
votes
1
answer
60
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 ...
- 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 ...
- 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 ...
- 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 ...
- 16.6k
0
votes
1
answer
68
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
...
-2
votes
2
answers
832
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 '...
- 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 ...
- 3,312
1
vote
1
answer
39
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 ...
- 153
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 ...
- 1,203
2
votes
3
answers
11k
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,...
-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 ...
- 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 ...
- 143
0
votes
2
answers
72
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 ...
- 23
1
vote
1
answer
7k
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.
- 11
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 ...
- 13
1
vote
1
answer
4k
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
==========...
- 111
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 " ...
- 127
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 ...
- 3,545
5
votes
1
answer
31k
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
...
- 247