T-SQL: Count Numbers of semicolons before expression

Question

I got a table with strings that look like that: '9;1;test;A;11002' How would I count how many semicolons are there before the 'A'?

Cheers!

Why do you want to know that? What is the *actual* problem you want to solve and why do you think knowing how many elements are there will help? — Panagiotis Kanavos, Oct 04 '21 at 07:18
Does this answer your question? [How do you count the number of occurrences of a certain substring in a SQL varchar?](https://stackoverflow.com/questions/738282/how-do-you-count-the-number-of-occurrences-of-a-certain-substring-in-a-sql-varch) — ekochergin, Oct 04 '21 at 07:18
@ekochergin this won't help at all. The OP is asking for the count up to `A`, not the total count. Otherwise `COUNT(*) from STRING_SPLIT()` would be more than enough. — Panagiotis Kanavos, Oct 04 '21 at 07:19
@PanagiotisKanavos I have multiple csv files i import via bulk-import and openrowset. they all have different structures and I need only one column - which is 'A' (so 'A' is a header). To import only that column I need to know where exactly in the file 'A' is. I import a textstring as above and the next row would be the data that is in '9;1;test;A;11002'. To extract the data that is in column A I need to know how many semicolons are before A so I can cut out everything before and after in the next row to get the data within A. — vincevangone, Oct 04 '21 at 07:42
@vincevangone **don't** store raw CSV data in a single field. It's as simple as that. You can't query the data any more. What you try to do is infinitely harder and more expensive that creating proper tables. `i import via bulk-import` which means you can just map the various files to tables. If you make sure the fields and names map, you can import the data with a single command. `I need to know how many semicolons are before A` if you used proper table you could just write `Select ThatColumn` — Panagiotis Kanavos, Oct 04 '21 at 07:51

score 3 · Answer 1 · answered Oct 04 '21 at 07:22

3

Using string functions

select len(left(str,charindex(str,'A')) - len(replace(left(str,charindex(str,'A'), ';', '')) n
from tbl

answered Oct 04 '21 at 07:22

Serg

22,285
5
21
48

score 0 · Answer 2 · answered Oct 04 '21 at 08:27

Hint1: The whole issue has some smell... You should not store your data as CSV string. But sometimes we have to work with what we have...
Hint2: The following needs SQL-Server v2016. With an older version we'd need to do something similar based on XML.

Try this:

--A declared table to mockup your issue

DECLARE @tbl TABLE(ID INT IDENTITY, YourCSVstring VARCHAR(100));
INSERT INTO @tbl(YourCSVstring) 
          VALUES('9;1;test;A;11002');

--the query

SELECT t.ID
      ,A.*
FROM @tbl t
CROSS APPLY OPENJSON(CONCAT(N'["',REPLACE(t.YourCSVstring,';','","'),N'"]')) A;

The idea in short:

We use some replacements to translate your CSV-string to a JSON array.
Now we can use use OPENJSON() to read it.
The value is the array item, the key its zero-based index.
Proceed with this however you need it.

Just to give you some fun: You can easily read the CSV type-safe into columns by doubling the [[ and using WITH to specify your columns:

SELECT t.ID
      ,A.*
FROM @tbl t
CROSS APPLY OPENJSON(CONCAT(N'[["',REPLACE(t.YourCSVstring,';','","'),N'"]]')) 
            WITH(FirstNumber  INT           '$[0]'
                ,SecondNumber INT           '$[1]'
                ,SomeText     NVARCHAR(100) '$[2]'
                ,YourLetterA  NVARCHAR(100) '$[3]'
                ,FinalNumber  INT           '$[4]')A

returns:

ID  FirstNumber SecondNumber    SomeText    YourLetterA FinalNumber
1   9           1               test        A           11002

T-SQL: Count Numbers of semicolons before expression

2 Answers2