In TSQL, the string in the database record is 'A/A/A' or 'A/B/A' (examples). I want to parse the string and for the first instance return '1'; in the 2nd instance, return '2'. That is, if all the values between the separators are the same, return a value; otherwise return another value. What is the best way to do this?

CodePudding user response：

A bit blind answer:

1. Read the whole value in a variable. Read the first value part in another:

   declare @entire nvarchar(max), @single nvarchar(max)

   select/set @entire=....

   set @single=left(@entire,charindex('/',@entire)-1)

2. Compare entire with @single replicated after removing slashes:

   set @entire=replace(@entire,'/','')

   select case when replicate(@single,len(@entire)/len(@single))=@entire

   then 1 else 0 end as [What you want]

CodePudding user response：

Something like this should work:

SELECT
    x.*,
    CASE
        WHEN N > 1 THEN 0
        ELSE 1
    END Result
FROM (
        SELECT 
            t.Column1,
            t.Column2,
            t.Column3,
            t.SomeColumn,
            COUNT(DISTINCT s.value) N
        FROM dbo.YourTable t
        OUTER APPLY STRING_SPLIT(t.SomeColumn,'/') s
        GROUP BY 
            t.Column1,
            t.Column2,
            t.Column3,
            t.SomeColumn
      ) x
;

CodePudding user response：

Based on your simple example (no edge cases accounted for) the following should work for you:

select string, iif(replace(s,v,'')='',1,0) as Result
from t
cross apply (
  values(left(string,charindex('/', string)-1),(replace(string,'/','')))
)s(v,s);

Example Fiddle