Searching strings in sql Column and need to split required string with prefix

Question

I have a table and having two columns account and rt_exclude.I need to select only the word "exclusion" from the rt_exclude column with the prefix.The rt_exclude column contains many rows with different length so i dont want to use case statement.

123 ,      278-C Share Exclusion; 3405-B Share Exclusion; 5784-Reit Exclusion
333 ,    278-C Share Exclusion; 3405-B Share Exclusion; 5784-Reit Exclusion; 6532-Exclude GIREX 39822J102
444,         3274-TSSAX Exclusion; 3370-BFAFX Exclusion; 3405-B Share Exclusion; 5784-Reit Exclusion

Required out put should be like this with the account column

123,    Share Exclusion
123,   Share Exclusion
123,   Reit Exclusion
333,    Share Exclusion
333,    Share Exclusion
123,    Reit Exclusion
444,   TSSAX Exclusion
444,   BFAFX Exclusion
444,   Reit Exclusion

score 1 · Answer 1 · answered Aug 26 '20 at 15:38

1

This looks like string_split():

select t.col1, s.value
from t cross apply
     string_split(col2, '; ') s
where s.value like '%Exclusion';

Note that storing multiple values in a single column is discouraged. You should reconsider the data model.

answered Aug 26 '20 at 15:38

Gordon Linoff

1,242,037
58
646
786

string_split is not working in my sql server version.Is there any other way for doing this.Thanks!! – Rajeev Aug 26 '20 at 16:15

score 0 · Answer 2 · answered Aug 26 '20 at 16:37

If you are on SQL Server 2016+ use string_split as suggested.
Getting this done in SQL Server 2014 and below you need a custom split function, e.g. dbo.SplitStrings_CTE from here or here on SO.

CREATE FUNCTION dbo.SplitStrings_CTE
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);
 
   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);
 
   RETURN;
END
GO

And the selection becomes

SELECT t.id
    ,SUBSTRING(s.Item, PATINDEX('%[^0-9- ]%', s.Item)+1, LEN(s.Item)) AS col
FROM docs t
CROSS APPLY SplitStrings_CTE(content, ';') s
WHERE s.Item LIKE '%Exclusion';

score 0 · Answer 3 · answered Aug 27 '20 at 12:58

Does this do what you want?

Select res.col1,   SUBSTRING(res.col2,CHARINDEX('-',res.col2)+CHARINDEX(' ',res.col2),99)
from 
(
select StringSplit.col1, value col2
from  StringSplit
Cross Apply  STRING_SPLIT(StringSplit.col2,';') as ss
WHERE StringSplit.col2 LIKE '%Exclu%') as res

Searching strings in sql Column and need to split required string with prefix

3 Answers3