user1425503 user1425503 - 4 months ago 10
SQL Question

How to count string between string in sql

I have a data that having different length of value.

For example I have 2 rows of data:

CL-CI/PST/102/VII/2016
CL-CI/PST/0102/VII/2016


The difference between them is 102 (3 digits) and 0102 (4 digits)

I want in my SQL checking:

if (3Digits)
begin
....
end
else
begin
....
end


The records of data format is not fixed. Just not fixed on this string VII/2016 (based on month in roman numerals/year).

I know how to count the string is using LEN. But my problem is when I select top 1 of data. In this top 1 data that I got, I want to check dynamically if it is 4 digits / 3 digits that I got from that top 1. I'm stuck on this.

Answer

Try it like this

Easy: Just the length

DECLARE @s VARCHAR(100)='CL-CI/PST/0102/VII/2016';
SELECT LEN(CAST('<x>' + REPLACE(@s,'/','</x><x>')+'</x>' AS XML).value('/x[3]','varchar(max)'))

The result is 4

setbased approach

DECLARE @tbl TABLE(ID INT IDENTITY,YourString VARCHAR(100),OtherValue VARCHAR(100));
INSERT INTO @tbl(YourString,OtherValue) VALUES
 ('CL-CI/PST/102/VII/2016','With 3 digits')
,('CL-CI/PST/0102/VII/2016','With 4 digits');

WITH ExtendByPart3 AS
(
    SELECT *
          ,CAST('<x>' + REPLACE(YourString,'/','</x><x>')+'</x>' AS XML).value('/x[3]','varchar(max)') AS Part3
    FROM @tbl
)
SELECT *,LEN(Part3) AS LenPart3 FROM ExtendByPart3

The result

ID  YourString              OtherValue      Part3   LenPart3
1   CL-CI/PST/102/VII/2016  With 3 digits   102     3
2   CL-CI/PST/0102/VII/2016 With 4 digits   0102    4

Btw: There are several questions about: How to access item X of a seperated string and most answers come with very complex CTEs, loops, CLR methods... This approach is direct and type safe. Change the nvarchar(max) of the .value() function to int and you would get the number - if needed.

I placed an answer to one of these questions myself, but - as this question is existing for years - the leading answers are very old fashioned and - IMO - outdated. But still it migth be worth reading this...

Retrieve all values

If you might be interested in your other values too, you could do this:

DECLARE @tbl TABLE(ID INT IDENTITY,YourString VARCHAR(100),OtherValue VARCHAR(100));
INSERT INTO @tbl(YourString,OtherValue) VALUES
 ('CL-CI/PST/102/VII/2016','With 3 digits')
,('CL-CI/PST/0102/VII/2016','With 4 digits');

WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(YourString,'/','</x><x>')+'</x>' AS XML) AS XMLData
    FROM @tbl
)
SELECT XMLData.value('/x[1]','varchar(max)') AS Part1
      ,XMLData.value('/x[2]','varchar(max)') AS Part2
      ,XMLData.value('/x[3]','int') AS Number
      ,XMLData.value('/x[4]','varchar(max)') AS MonthRoman
      ,XMLData.value('/x[5]','int') AS TheYear
FROM Splitted

The result (attention: as returned as int the Number is without the leading zero)

Part1   Part2   Number  MonthRoman  TheYear
CL-CI   PST     102     VII         2016
CL-CI   PST     102     VII         2016