CODEWITHSUNDEEP
sqlsql-serversql-server-2019
riad
riad

Reputation: 7194

How to get value using split string in SQL Server 2019

I have value in a column like below ( space separated string )

1 4 7 5
4 5
1 4
1 3 4

I just need that 1 is 3 times,3 is 1 time, 4 is 4 times 5 is 2 times & 7 is 1 times.

Like below:

Value| count
-----+-------
 1   |   3 
 3   |   1 
 4   |   4 
 5   |   2 
 7   |   1

I have tried like this:

SELECT value  
FROM [dbo].tbl  
CROSS APPLY STRING_SPLIT((SELECT DisCode FROM tbl), ' ');

but I get an error

Invalid object name 'STRING_SPLIT'

I have tried: String_split function is not splitting delimiters in SQL Server 2019 but did not get any solution.

Somebody asked about the lowest the compatibility level, but it's not possible for me to downgrade by server level because it's a running online server.('STRING_SPLIT' is not a recognized built-in function name )

Please give me a possible solution.

Upvotes: 1

Views: 9431

Answers (3)

SteveC
SteveC

Reputation: 6015

Prior to SQL 2016 one way to split strings is/was to use a 'Tally' function or 'numbers' table approach. In this code the CTE 'numbers_cte' contains 20 rows. The outer query uses CROSS JOIN to create up to 400 (20^2) rows (if more are needed then add more CROSS JOINs, as needed). Using a row goal (SELECT TOP(n)) means the un-used rows are never generated so the query is very efficient. Something like this

declare @txtTable   table(txt       varchar(50) not null)

insert @txtTable(txt) values 
('1 4 7 5'),
('4 5'),
('1 4'),
('1 3 4');

with numbers_cte(n) as (
    select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                          (11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) v(n))
select v.chr, count(*) as char_count
from @txtTable t
     cross apply (select top(len(t.txt)) row_number() over (order by (select null)) as N
                  from numbers_cte n1
                       cross join numbers_cte n2) fn
     cross apply (values (substring(t.txt, N, 1))) v(chr)
where
  v.chr <> ' '
group by v.chr;

chr char_count
1   3
3   1
4   4
5   2
7   1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

STRING_SPLIT() is available starting in SQL Server 2016, which means that it requires compatibility level 130 or higher (see the documentation).

A database running on SQL Server 2019 can run with a variety of compatibility levels even lower than 130. You can check the compatibility level of your database by using:

SELECT compatibility_level  
FROM sys.databases
WHERE name = db_name();

If this is less than 130, then you will not have access to the function, unless you change the compatibility or create a new database with a higher compatibility level.

Upvotes: 1

Squirrel
Squirrel

Reputation: 24783

STING_SPLIT is available in SQL Server 2019. Your compatibility level must be set as a much earlier version. STRING_SPLIT should be available from SQL Server 2016 / Compatibility Level 130 onwards.

Also your query should be

SELECT  value, count(*)
FROM    tbl 
        CROSS APPLY STRING_SPLIT (DisCode, ' ')
GROUP BY value

STRING_SPLIT (Transact-SQL)

Upvotes: 2

Related Questions