Sql Select Rows Containing Part of String

Sql select rows containing part of string

SELECT *
FROM myTable
WHERE URL = LEFT('mysyte.com/?id=2®ion=0&page=1', LEN(URL))

Or use CHARINDEX
http://msdn.microsoft.com/en-us/library/aa258228(v=SQL.80).aspx

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

If you need all words to be present, use this:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
  AND column1 LIKE '%word2%'
  AND column1 LIKE '%word3%'

If you want something faster, you need to look into full text search, and this is very specific for each database type.

SQL: Select rows that contain a word

Hi you could grab with trailing space and with leading space

SELECT * from new_table
where text RLIKE(' test')
union
SELECT * from new_table
where text RLIKE('test ')

SQL select rows containing substring in text field

You get better performance with unnest() and JOIN. Like this:

SELECT DISTINCT c.client_id
FROM   unnest(string_to_array('Some people tell word1 ...', ' ')) AS t(word)
JOIN   clients_words c USING (word);

Details of the query depend on missing details of your requirements. This is splitting the string at space characters.

A more flexible tool would be regexp_split_to_table(), where you can use character classes or shorthands for your delimiter characters. Like:

regexp_split_to_table('Some people tell word1 to someone', '\s') AS t(word)
regexp_split_to_table('Some people tell word1 to someone', '\W') AS t(word)

• Related answer: Django. PostgreSQL. regexp_split_to_table not working
• A search for more answers for regular expression class shorthands.

Of course the column clients_words.word needs to be indexed for performance:

CREATE INDEX clients_words_word_idx ON clients_words (word)

Would be very fast.

Ignore word boundaries

If you want to ignore word boundaries altogether, the whole matter becomes much more expensive. LIKE / ILIKE in combination with a trigram GIN index would come to mind. Details here:

PostgreSQL LIKE query performance variations

Or other pattern-matching techniques - answer on dba.SE:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

However, your case is backwards and the index is not going to help. You'll have to inspect every single row for a partial match - making queries very expensive. The superior approach is to reverse the operation: split words and then search.

Select rows with any member of list of substrings in string

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

SQL SELECT rows in a table if the given condition is a substring of the column data with a separator ';' between the substrings

TL;DR;
Here is the query that you want

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data = 'abc@mail'
)

I recommend the splitting approach. Any character lookup will have to account the variability of the semi-colons, whereas splitting it out will handle the ambiguity of where the semi-colons are, and then you can do a direct equality check. If you wanted to take it a step further and look for additional [To] addresses you can just add an IN clause like this and SQL Server doesn't have to do much more work and you get the same results.

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data IN ('abc@mail', 'def@mail')
)

My answer is fairly similar to @Kitta answer in that we split the data out, and @Kitta is correct about the IN clause, but while their answer will work it will require you grouping your data back together to get a singular answer. Using the EXISTS clause will bypass all of that for you and only give you the data from the original table. That being said, please mark @Kitta as the answer if their answer works just as well for you.

Here is the test setup that I used

DROP TABLE Mails
GO
CREATE TABLE Mails
([To] VARCHAR(3000))

INSERT INTO dbo.Mails
(
    [To]
)
VALUES
('123@mail;abc@mail;aabc@mail')
,('nottheone@mail.com')
,('nottheone@mail.com;Overhere@mail.com')
,('aabc@mail;ewrkljwe@mail')
,('ewrkljwe@mail')

GO
DROP FUNCTION [split_string]
GO
CREATE FUNCTION [dbo].[split_string]
(
@string_value NVARCHAR(MAX),
@delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
DECLARE @start_position INT,
        @ending_position INT
SELECT @start_position = 1,
        @ending_position = CHARINDEX(@delimiter_character, @string_value)
WHILE @start_position < LEN(@string_value) + 1
        BEGIN
    IF @ending_position = 0 
       SET @ending_position = LEN(@string_value) + 1
    INSERT INTO @result_set (splited_data) 
    VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
    SET @start_position = @ending_position + 1
    SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
END
RETURN
END
GO

SELECT *
FROM dbo.Mails AS m
WHERE EXISTS (
    SELECT *
    FROM dbo.split_string(m.[To], ';') s
    WHERE s.splited_data = 'abc@mail'
)
    

and it returns the correct row of '123@mail;abc@mail;aabc@mail'

---

Related Topics