How to count string between string in sql

Question

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.

Is this a fixed format or the format of data may differ from record to record? — Deep, Jul 11 '16 at 10:23
For this string VII/2016 (based on month(roman numerals)/year) is not fixed — Ras Rass, Jul 11 '16 at 10:26
fixed is not a count of chars, fixed is that your string is between second `/` and third `/` like in this article http://stackoverflow.com/questions/17975150/substring-of-variable-length — xdd, Jul 11 '16 at 10:29
And what is there happening in that `if 3digits` block which is so specific enough for being separated from all other scenarios? Adding leading zero? — Ivan Starostin, Jul 11 '16 at 10:41
I want to make sequence no, but it depends on the last record that I got. For example I got CL-CI/PST/102/VII/2016, so the next new seq number is CL-CI/PST/0103/VII/2016. and when I select top 1 again for example I got CL-CI/SPG/069/VII/2016, so the next seq number must CL-CI/SPG/0068/VII/2016. The 3 digits data is the old format version from production. I have to change to 4 digits format. This format number is per-branch. That's why I want to check using if condition. But any idea will be welcome, — Ras Rass, Jul 12 '16 at 01:17

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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

score 1 · Answer 2 · answered Jul 11 '16 at 11:02

1

Perhaps case and like are sufficient:

select (case when col like '%/%/[0-9][0-9][0-9]/%' then <3 digit stuff>
             when col like '%/%/[0-9][0-9][0-9][0-9]/%' then <4 digit stuff>
        end)

answered Jul 11 '16 at 11:02

Gordon Linoff

1,242,037
58
646
786

How to count string between string in sql

2 Answers2

Easy: Just the length

setbased approach

Retrieve all values