0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
riad
  • 7,144
  • 22
  • 59
  • 70
  • 3
    `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` – Squirrel Jan 17 '21 at 10:17
  • 1
    What version are you running `select @@version`? @Squirrel `STING_SPLIT` is certainly ***NOT*** available, `STRING_SPLIT` is available from 2016 already – Charlieface Jan 17 '21 at 10:21
  • My version is: Microsoft SQL Server 2019 (RTM-GDR) . I am unable to downgraded the compatibility level. – riad Jan 17 '21 at 11:44
  • @Charlieface lol. Typo error – Squirrel Jan 18 '21 at 00:40
  • We are not asking you to downgrade the `compatibility level` but to change it to minimum 130 if you want to use the `STRING_SPLIT()` – Squirrel Jan 18 '21 at 00:41

3 Answers3

2

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)

Squirrel
  • 23,507
  • 4
  • 34
  • 32
0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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
SteveC
  • 5,955
  • 2
  • 11
  • 24