How to Split string into list in SQL for filtering?

Question

I have a function, where a sentance is passed in. I want to pull data only if the word is found in that sentance. Heres what I have:

select 
    word_data
from words
where
    words.word in STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Does anyone know the correct way to do this?

STRING_SPLIT returns a dataset, not a scalar value. It goes in the FROM. Have you read the documentation and its examples? It demonstrates exactly how to do what you're after. — Thom A
– Thom A ♦, Commented Feb 1, 2021 at 13:14
Side note: The last "word" in your query will be treated as 'amet.' not 'amet'. If you have the word 'amet' in your column word it will not be matched. — Thom A
– Thom A ♦, Commented Feb 1, 2021 at 13:16
to add to what @Larnu said you'll want to handle any punctuation that exists in your string. — Bee_Riii
– Bee_Riii, Commented Feb 1, 2021 at 13:17

Michał Lipok · Accepted Answer · 2021-02-01 15:59:35Z

2

According documentation expression have to be like

SELECT
    [word_data]
FROM
    [words]
WHERE
    [words].[word] IN (
            SELECT
                [value]
            FROM
                STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ')
        );

and same result via JOIN

SELECT
    [word_data]
FROM
    [words]
INNER JOIN (
        SELECT
            [value]
        FROM
            STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ')
    ) AS [s] ON [s].[value] = [words].[word];

edited Feb 1, 2021 at 15:59

Michał Lipok

3879 silver badges28 bronze badges

answered Feb 1, 2021 at 13:17

Aziz Umarov

3162 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Split string into list in SQL for filtering?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related