T-SQL: Count Numbers of semicolons before expression-CodePudding

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!

CodePudding user response：

Using string functions

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

CodePudding user response：

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