Home Select rows with any member of list of substrings in string
Reply: 1

Select rows with any member of list of substrings in string

OriginalWombat
1#
OriginalWombat Published in 2017-11-14 14:27:05Z

In a Micrososft SQL Server table I have a column with a string.

Example:

'Servernamexyz.server.operationunit.otherstuff.icouldnt.predict.domain.domain2.domain3'

I also have a dynamic list of substrings

Example:

('icouldnt', 'stuff', 'banana')

I don't care for string manipulation. The substrings could also be called:

('%icouldnt%', '%stuff%', '%banana%')

What's the best way to find all rows where the string contains one of the substrings?

Solutions that are not possible:

  • multiple OR Statements in the WHERE clause, the list is dynamic
  • external Code to do a "for each", its a multi value parameter from the reportbuilder, so nothing useful here
  • changing the database, its the database of a tool a costumer is using and we can't change it, even if we would like... so much

I really cant believe how hard such a simple problem can turn out. It would need a "LIKE IN" command to do it in a way that looks ok. Right now I cant think of anything but a messy temp table.

Cool_Br33ze
2#
Cool_Br33ze Reply to 2017-11-14 15:25:38Z

One option is to use CHARINDEX

DECLARE @tab TABLE (Col1 NVARCHAR(200))
INSERT INTO @tab (Col1)
VALUES (N'Servernamexyz.server.operationunit.otherstuff.icouldnt.predict.domain.domain2.domain3' )

;WITH cteX
AS(
    SELECT 'icouldnt' Strings
    UNION ALL
    SELECT 'stuff'
    UNION ALL
    SELECT 'banana'
)
SELECT
    T.*, X.Strings
FROM @tab   T
CROSS APPLY (SELECT X.Strings FROM cteX X) X
WHERE CHARINDEX(X.Strings, T.Col1) > 1

Output

EDIT - using an unknown dynamic string variable - @substrings

DECLARE @tab TABLE (Col1 NVARCHAR(200))
INSERT INTO @tab (Col1)
VALUES (N'Servernamexyz.server.operationunit.otherstuff.icouldnt.predict.domain.domain2.domain3' )

DECLARE @substrings NVARCHAR(200) = 'icouldnt,stuff,banana'

SELECT
    T.*, X.Strings
FROM @tab   T
CROSS APPLY 
(   --dynamically split the string
    SELECT Strings = y.i.value('(./text())[1]', 'nvarchar(4000)')
    FROM 
    ( 
    SELECT x = CONVERT(XML, '<i>' 
        + REPLACE(@substrings, ',', '</i><i>') 
        + '</i>').query('.')
    ) AS a CROSS APPLY x.nodes('i') AS y(i)
) X
WHERE CHARINDEX(X.Strings, T.Col1) > 1
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332394 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO