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.', ' ', 1); The error is: > Msg 8748, Level 16, State 1, Line 1 The enable_ordinal argument for > STRING_SPLIT only supports constant values (not variables or columns). The production environment is: Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) Nov 14 2023 18:33:19 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) The version of the SSMS is: We have tested the code on the latest CU: The only thing I have found is this answer pointing that: > The problem is that SSMS has IntelliSense / tooltips coded without > conditional logic based on version, and the code is ahead of the > engine. Currently the functionality is only available in Azure SQL > Database, Managed Instance, and Synapse. Still, I am not sure where is the issue - the docs, the engine, the SSMS or something I am not doing right. Note: the issue is the same on Azure Data Studio.

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 table. At that point, I will join the temp table with the Customer table to return the *CustomerId*. I just want to see if there is a better way to do this. Data In *SiteConfiguration* Table

-none
123@123.com;456@456.com

Data in *Customer* table

-none
123@123.com 1
456@456.com 2

Code: declare @AdditionalVoteViewers VARCHAR(100) Select @AdditionalVoteViewers = ( SELECT [Value] FROM [SiteConfiguration] where Name = 'AdditionalVoteViewers' ) print @AdditionalVoteViewers --CREATE TABLE #TempAdditionalVoteViewers(email varchar(MAX) ) select * into #TempAdditionalVoteViewers from [dbo].[Split] (@AdditionalVoteViewers,';') --select * from #TempAdditionalVoteViewers select [CustomerId] into #TempAdditionalVoteViewersId from [dbo].[Customer] c join #TempAdditionalVoteViewers t on c.Email = t.val select * from #TempAdditionalVoteViewersId

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 13k rows with Input as ( select Value, Row = row_number() over (order by (select null)) - 1 from string_split(@dataCsv, ',') o ) --insert into PubCache.TableName select 78064 as CacheId, convert(bigint, i.Value) as ObjectId, convert(smallint, i2.Value) as BrandId from Input i inner hash join Input i2 -- hash to encourage string_split() only once per column on i2.Row = i.Row + 1 where i.Row % 2 = 0 order by i.Row Execution Plan: https://www.brentozar.com/pastetheplan/?id=By0hYPmd6 As shown in the plan, the evaluation of the convert() is occurring before the join so sometimes (depending on length of input), it fails with > The conversion of the nvarchar value '37645' overflowed an INT2 > column. Use a larger integer column. Temporarily changing the conversion from smallint to int allows the query to complete and inspecting the BrandId column output shows that it always just contains the value '36' for this example. Is there a simple way to delay the convert(smallint, i2.Value) until the after the join so that only those expected CSV positions are converted? I realize there are other ways to pack a string stream (such as using multiple variables, or interlacing different split chars, etc) but am not interested in solving this example that way for purposes of this question. Thanks!

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 the columns into rows and the output should be this: | id | Column B |column c | | -------- | -------- |-------- | | 1 | a | 1 | | 1 | b | 2 | | 1 | c | 3 | | 2 | d | 4 | | 2 | | 5 | | 2 | f | 6 | | 3 | g | 7 | | 3 | h | | | 3 | i | 9 | | 3 | j | 8 | I have just given the idea of how to convert these into rows, but my actual columns are more than 30 that need to be separated by comma.

I have this column and rows I want to split the text in the row into separate columns like this.

I have a column of strings with this pattern - . Email is always the first string. I would like to extract only the email address but the problem here is that an email address can also contain hyphens so I can't be certain that the delimiter will only occur once. So basically I would like to match .* until the last hyphen and extract this as email. Well it's not exactly about administration, it's about writing a query to extract data so it's in data mining area, however this forum is related to database entirely so I think it' s more appropriate than stackoverflow. I only tried with SUBSTRING_INDEX() but I ended up getting bad results with it. It's a production system so I can't really interfere with the design, hence the need to extract the info.

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 **Expected Results** ID|Rack|RequiredQty|FulfilledQty -|-|-|- 1|A|60|60 1|B|102|39 1|C|8|0 2|A|10|0

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 are no before and after | numbers, like the second row | SE +17.5D CYL1.25 update columns T1 and T2 null.

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,7 8,9,10 10,11,15 expected_outcome 1,2,3,4,5,7 8,9,10,11,15 We are simply trying to match a group of numbers based on if a single number matches in any other group. Then merge all of these groups. For instance. If we have: ('1,2,3'), ('1,4'), ('1,5,7') We want: (1,2,3,4,5,7) We consolidate them into a single line in PostgreSQL. or (another example): ('8,9,10'), ('10,11,15') desired output: (8,9,10,11,15) The query would group these numbers because they have the number 10 in common. But it wouldn't group with the previous row (i.e. (1,2,3,4,5,7)) that don't share a number. When we have these groups in a table. They will only group together if they at least have one matching number in each group. ======== DDL and DML ============= create table current (current_data text not null); create table expected_output (expected_outcome text not null); insert into current (current_data) values ('1,2,3'),('1,4'),('1,5,7'),('8,9,10'), ('10,11,15'); insert into expected_output (expected_outcome) values ('1,2,3,4,5,7'),('8,9,10,11,15');

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 following set: store_id | emails --- | --- 1 | user_1@example.com 1 | user2@example.com 2 | uswe3@example.com 2 | user4@example.com 4 | admin@example.com As you can see I want to split the emails field into a separate record containing only one email address. Do you have any idea how I can do that? So far I managed to create the following query:

select store_id,string_to_array(emails,',') from stores

But I don't know how I can split the string_to_array to its own row.

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

(let's say), there is a

column mapped to a

-options

column. In the latter, data is saved as

#3#9#15#

where 3, 9 and 15 are IDs of dropdown menue options that have been selected by a user. There is another table (let's say

) where each of these options with IDs 1 to 15 are given more attributes (like a

and a

). For statistical reasons, I want to create a MySQL view with the columns

option

and

of products

(the number of products where this option has been selected). To get there, I need the single values from

#3#9#15#

, that is, explode the string somewhere on the way. Now I read that this form of saving data is not encouraged, which is why there is no built-in explode/split function for strings in MySQL. But I cannot change the data format, which has been determined inside a huge software. I didn't make this design choice, but I have to somehow work with it. Working with the built-in function

()

is no choice because the number of selected options saved in this field varies, from 0 to 15. Also, the maximum number is not fix, as new options may be created from time to time. I tried writing an own procedure that will take one number from

#3#9#15#

(for example, by repeatedly trimming the # delimiters) and create a new row with it. The procedure should access the value with a

statement, create a new view with

OR REPLACE VIEW

, and then insert a new row with only the first option (3) option with

INTO viewname(product_id,option) VALUES(@productID, SUBSTR(@options,2,1));

where

@options == #3#9#15#

for example. I tried writing the procedure for only the first option at the beginning, but it already failed at this stage. Other ideas I had included trying to convert the string

@options

into a SET datatype, so I can use

()

function. I also read this question and this article but found them very hard to understand. Having only basic knowledge of MySQL, is there some way for me to create this view? I would be very thankful for any hint in which direction I should go on researching. ***Edit***: I have server version 10.3.28 - MariaDB Server. Do you need more information on this? ***Edit2***: Due to an (ongoing) bug in phpMyAdmin, I could not use SQL

statement (see here ). ***Edit3***: It turned out that the delimiter used by the software was not actually a

#

sign, but was only displayed by phpMyAdmin as such. It was instead the ASCII control character 0001 or "^A".

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 in the database Here is sample table and data CREATE TABLE #temp (id INT, keys VARCHAR(50), vals VARCHAR(50) ); INSERT INTO #temp VALUES (1, '1,2,3', 'one,two,three'), (2, '4,5,6', 'four,five,six'), (3, '7,8,9', 'seven,eight,nine'); and my desired output would be ID key val 1 1 one 1 2 two 1 3 three 2 4 four 2 5 five 2 6 six 3 7 seven 3 8 eight 3 9 nine I got the query to work if I only split one column, so I define two CTEs with row_number and join on ID and row_number. This does give desired output but my live table is very large and I was hoping for a way to pass through the table only once, instead of twice. with keys as( SELECT id,keys,vals, keys.keyid.value('.', 'VARCHAR(8000)') AS keyid, row_number() over(order by (select null)) as rn FROM (SELECT id,keys,vals, CAST(''+REPLACE(keys, ',', '')+'' AS XML) AS tempkeys FROM #temp ) AS temp CROSS APPLY tempkeys.nodes('/Keys/key') AS keys(keyid)), vals as( SELECT id,keys,vals, vals.val.value('.', 'VARCHAR(8000)') AS valid, row_number() over(order by (select null)) as rn FROM (SELECT id,keys,vals, CAST(''+REPLACE(vals, ',', '')+'' AS XML) AS tempvals FROM #temp ) AS temp CROSS APPLY tempvals.nodes('/vals/val') AS vals(val)) SELECT k.id, k.keyid, v.valid FROM keys AS k INNER JOIN vals AS v ON k.id = v.id AND k.rn = v.rn;

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).

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 " ". And then split the rest of the string with comma (,) I don't want to do it checking every single character setting flags for start and end of double quotes. Can i implement some sort of regex? Is there a simple way? What I have tried so far is just to split the string based on the comma (,) but it is also splitting values inside the quotes. This is serving the purpose: how can I implement this in complete table (currently it is working if I have only one instance of double quotes block)? Declare @Query nvarchar(max) Set @Query = 'Item1,Item2,"Item,Demo,3",New' Declare @start int, @len int SELECT @start = PATINDEX('%"%"%', @Query) + 1 print @start select @len = CHARINDEX('"', SUBSTRING(@Query, @start, LEN(@Query))) - 1 select SUBSTRING(@Query, 1, @start - 2) + REPLACE((SUBSTRING(@Query, @start, @len)), ',', '') + SUBSTRING(@Query, @start + @len + 1, LEN(@Query)) This is the function I am using to split ALTER FUNCTION [dbo].[fnSplit]( @sInputList VARCHAR(8000) -- List of delimited items , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items ) RETURNS @List TABLE (id int, item VARCHAR(8000)) BEGIN DECLARE @sItem VARCHAR(8000) Declare @ID as int Set @ID=0 WHILE CHARINDEX(@sDelimiter,@sInputList,0) 0 BEGIN SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) Set @ID=@ID+1 IF LEN(@sItem) > 0 INSERT INTO @List SELECT @ID,@sItem END IF LEN(@sInputList) > 0 INSERT INTO @List SELECT @ID,@sInputList -- Put the last item in RETURN END The problem is that this is an application in MS Access, which narrows down my possibilities. All I can do is passing the name of the file which is going to get imported by SQL Server.

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 code. The outcome is a CPU time of about 90000 vs about 15000 (according to dm_exec_query_stats), so the difference is enormous. I have posted the 2 plans here... 1. Hardcoded Plan 2. String Split Plan The interesting thing is the query plan shows the cost as being pretty much the same, but in when I check dm_exec_query_stats the cost (last_worker_time) is massively different. Here are 2 outputs from the query plan... 0x79DEAD79D1F149CD 16199 select * from fn_get_samples(1) s where s.sample_id in (2495,2496,2497,2498,2499,2500,2501,2502,2503,2504) 0x4A073840486B252C 86689 select * from fn_get_samples(1) s where s.sample_id in (select value as id from STRING_SPLIT('2495,2496,2497,2498,2499,2500,2501,2502,2503,2504',',')) The function code is...

CREATE FUNCTION [dbo].[fn_get_samples]
(
	@user_id int
)
RETURNS TABLE
AS
RETURN (
	-- get samples
	select s.sample_id,language_id,native_language_id,s.source_sentence,s.markup_sentence,s.latin_sentence,
		s.translation_source_sentence,s.translation_markup_sentence,s.translation_latin_sentence,
		isnull(sample_vkl.knowledge_level_id,1) as vocab_knowledge_level_id,
		isnull(sample_gkl.grammar_knowledge_level_id,0) as grammar_knowledge_level_id,
		s.polite_level_id,
		case when isnull(tr1.leitner_deck_index,0)=0 then 0 else cast((tr1.leitner_deck_index-1)  as float)/cast((max_leitner_deck_index-1) as float) end as progress_percentage,
		case when isnull(tr2.leitner_deck_index,0)=0 then 0 else cast((tr2.leitner_deck_index-1)  as float)/cast((max_leitner_deck_index-1) as float) end as listening_progress_percentage,
		case when f.object_id is null then 0 else 1 end as is_favorite,
		case when st.object_id is null then 0 else 1 end as is_studied,
		s.has_error,
		s.is_deleted,
		f.create_datetime as favorite_datetime,
		st.create_datetime as studied_datetime,
		s.create_user_id,
		s.create_datetime,
		isnull(s.modify_user_id,s.create_user_id) as modify_user_id,
		isnull(s.modify_datetime,s.create_datetime) as modify_datetime,
		s.display_order
		from samples s
		left outer join sample_knowledge_level_votes klv on klv.sample_id=s.sample_id and klv.user_id=@user_id
		left outer join favorites f on f.user_id=@user_id and f.object_type_id=(select object_type_id from object_types ot where ot.object_type_name='Pattern Sample') and f.object_id=s.sample_id
		left outer join studied st on st.user_id=@user_id and st.object_type_id=(select object_type_id from object_types ot where ot.object_type_name='Pattern Sample') and st.object_id=s.sample_id
		left outer join leitner_tracking tr1 on tr1.user_id=@user_id and tr1.object_type_id=(select object_type_id from object_types ot where ot.object_type_name='Pattern Sample') and tr1.object_id=s.sample_id and tr1.skill_type_id=(select skill_type_id from skill_types where skill_type_name=N'Guess Pronunciation from Meaning')
		left outer join leitner_tracking tr2 on tr2.user_id=@user_id and tr2.object_type_id=(select object_type_id from object_types ot where ot.object_type_name='Pattern Sample') and tr2.object_id=s.sample_id and tr2.skill_type_id=(select skill_type_id from skill_types where skill_type_name=N'Guess Meaning from Pronunciation')
		cross join (select max(leitner_deck_index) as max_leitner_deck_index from leitner_decks) dm
		left outer join vw_sample_user_grammar_kl sample_gkl on sample_gkl.user_id=@user_id and sample_gkl.sample_id=s.sample_id
		left outer join vw_sample_avg_kl sample_vkl on sample_vkl.sample_id=s.sample_id
		where is_deleted=0
)

It appears to be something to do with the 'vw_sample_avg_kl' join. If I comment out that join and the calculated column for 'vocab_knowledge_level_id' then the two query timings become very similar. Soon as I add it back it they differ greatly. Here is the code for that view...

CREATE VIEW [dbo].[vw_sample_avg_kl]
	AS 
	select sample_id,knowledge_level_id from (
		select sample_id,knowledge_level_id,count(*) as frequency,RANK() over (partition by sample_id order by count(*) desc,knowledge_level_id) as myrank
		from sample_knowledge_level_votes
		group by sample_id,knowledge_level_id
	) sample_kl_ranking
	where myrank=1

The id field is INT. My two queries when shown in dm_exec_query_stats look like this...

0x4A073840486B252C	41096	select * from fn_get_samples(@user_id) s    where s.sample_id in (select * from STRING_SPLIT(@sample_id_list,','))

0x79DEAD79D1F149CD	7849	select * from fn_get_samples(1) s where s.sample_id in (2495,2496,2497,2498,2499,2500,2501,2502,2503,2504)

(slightly different example data set from above hence the slightly different timings, but you can see the huge gap in performance)

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 like to form a query along the lines of: SELECT * FROM table1 WHERE ID in table0.params I am using Microsoft SQL Server Standard Edition (64-bit) version 10.50.1600.1

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 back from the original array the values foo and hello world filtering out bar. Is there any way to get that with Postgres?

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 delimited by ':' From the example, I would like to get D:E field. T1 id list 0 A:B:C 1 D:E 2 F:G T2 vals A B C F G

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 this: declare @bk nvarchar(1000) set @bk = 'SIS5.0~person_id~696969' ;with hizizzle as ( select left(@bk, charindex('~', @bk)-1) as flippity, right(@bk, len(@bk) - charindex('~', @bk)) as floppity) select flippity, left(floppity, charindex('~',floppity)-1) as floppity, right(floppity, len(floppity) - charindex('~', floppity)) as flooo from hizizzle ...but there must be a better way, right? **Question:** How did you do string splits in the ancient past before 2014?

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 compatibility level, I can't use STRING_SPLIT. I also cannot create any globally available functions. I can however create a temporary table if needed. What's my course of action? Some sort of nesting of WHILE loops (One for the rows of the table, another for the semicolons in the string)? update: This is perhaps a better explanation of what I want to achieve: Let's say I have the following tables.

CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);

CREATE TABLE Customer
(
	CustomerId int
	, CustomerName varchar(30)
);

With some data

INSERT INTO Customer (CustomerId, CustomerName)
VALUES 	(1, 'John')
	,(2, 'Niels')
	,(3, 'Frank')
	,(4, 'Barbie')
	,(5, 'Holly')
	,(6, 'Ebeneezer');

INSERT INTO Service (ServiceId, CustomerIds)
VALUES	(1, '4')
	,(2, '2;3');

And let's say I want to select Customer.CustomerName for any Customer who's ID shows up in the CustomerIds in any line in that table. That is, I want to retrieve Niels, Frank and Barbie.