4

I would like to be able to split a string in SQL Server . I have a sample string

1012,1012,1012,1012,1012,1012,1012,1012

Here need length after splitting this string.

Expected output: 8
Shohel
  • 3,886
  • 4
  • 38
  • 75
  • 1
    Can you say the expected output – yuvi Jun 15 '16 at 05:06
  • @RajaYuvi, expected output length:8 – Shohel Jun 15 '16 at 05:08
  • if this is the input '1012,1012,1012,1012,1012,1012,1012,1012,1011,1011,1011,1011'.what wil be the expected output?? – yuvi Jun 15 '16 at 05:11
  • 1
    Possible duplicate of [How do you count the number of occurrences of a certain substring in a SQL varchar?](http://stackoverflow.com/questions/738282/how-do-you-count-the-number-of-occurrences-of-a-certain-substring-in-a-sql-varch) – Arun Prasad E S Jun 15 '16 at 05:35

2 Answers2

10

If you only need the number of items in a delimited string, you don't need to split it at all - here is how to do it:
You subtract the length of the string after removing all the delimiters from the length of the original string. This gives you the number of delimiters in the string. Then all you have to do is add one, since you have one more item then delimiters:

DECLARE @String varchar(50) = '1012,1012,1012,1012,1012,1012,1012,1012'

SELECT LEN(@String) - LEN(REPLACE(@String, ',', '')) + 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
5

I think, to just find the total items in the string you may no need to split the string. Just need to find the number of occurrence of ,.

Query

declare @string varchar(100)
set @string = '1012,1012,1012,1012,1012,1012,1012,1012'

select len(@string ) - len(replace(@string ,',', '')) + 1;

Demo

Community
  • 1
  • 1
Ullas
  • 11,450
  • 4
  • 33
  • 50