CODEWITHSUNDEEP
sqlsql-server
Niks
Niks

Reputation: 1007

Extracting matched word from string in sql

I would like to extract matched word from string. For example

Str = "This is Nihar Kulkarni as Indian"

Here using LIKE we will fetch all records those contain "%India%", but here I would like extract whole word which matched. Suppose from these string India matched so I want "Indian" as output.

Thanks

Upvotes: 0

Views: 2006

Answers (4)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following query. Here first I have inserted all words of given string into a table by separating space. After that using like matching records from that table has been selected.

DECLARE @Stringtofindmatching VARCHAR(MAX) = 'This is Nihar Kulkarni as Indian'
DECLARE @table TABLE ( matchingword VARCHAR(50) )
DECLARE @x INT = 0
DECLARE @firstspace INT = 0
DECLARE @nextspace INT = 0

SET @x = LEN(@Stringtofindmatching) - LEN(REPLACE(@Stringtofindmatching, ' ', '')) + 1 -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextspace = CASE WHEN CHARINDEX(' ', @Stringtofindmatching, @firstspace + 1) = 0
                              THEN LEN(@Stringtofindmatching) + 1
                              ELSE CHARINDEX(' ', @Stringtofindmatching, @firstspace + 1)
                         END
        INSERT  INTO @table
        VALUES  ( SUBSTRING(@Stringtofindmatching, @firstspace + 1, (@nextspace - @firstspace) - 1) )
        SET @firstspace = CHARINDEX(' ', @Stringtofindmatching, @firstspace + 1)
        SET @x = @x - 1
    END

SELECT  *
FROM @table where matchingword like '%India%'

You can find the live demo Here.

Upvotes: 0

Milad
Milad

Reputation: 117

You can try this for any word that you like :

DECLARE @MatchedWord VARCHAR(50) = 'Indian'
DECLARE @Str  VARCHAR(50) = 'This is Nihar Kulkarni as Indian'

SELECT SUBSTRING(@Str,CHARINDEX(@MatchedWord,@Str),LEN(@MatchedWord))

Upvotes: 1

Rahul Chanda
Rahul Chanda

Reputation: 35

You could try with query below:

select * from table_name where Str LIKE 'India%'

Upvotes: 0

Thom A
Thom A

Reputation: 95554

Something like this?

DECLARE @Word varchar(10) = 'India'

SELECT SUBSTRING(V.S,CIw.I, ISNULL(NULLIF(CIs.I,0),LEN(V.S)+1) - CIw.I)
FROM (VALUES('This is Nihar Kulkarni as Indian')) V(S)
     CROSS APPLY (VALUES(CHARINDEX(@Word,V.S))) CIw(I)
     CROSS APPLY (VALUES(CHARINDEX(' ',V.S,CIw.I))) CIs(I);

This gets the position of the word, and then the next space. If there is no space afterwards, the length of the full value (+1) is used. Note that if you have grammar in your value, for example 'This is Nihar Kulkarni, who is Indian.' or 'He is Indian, but lives in Europe' then the value 'Indian.' or 'Indian,' would be returned respectively.

Upvotes: 2

Related Questions