find all substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

find all substring

Author

Topic

sanjeevka08
Starting Member

18 Posts

Posted - 2014-05-15 : 01:52:23

I want to find all substrings from a string and return those substrings which are available in a sql table column with like search.

Please provide me query for that

for example: if my main string is iamabitlatefo ramovieatmall.
it must get words i,iam,ima,imab,iamabi,iamabit.....,am,ama,amab.......mab,mabit.....etc
and search the words in a sql table column and return those only matches with sql table data.
the final output may be i,am, a ,bit,late,for,movie,at and mall

please provide me a query or SP

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-15 : 02:44:40

can you post some sample data and expected output?

1) Table's Sample Data in the consumable format(INSERT statements)
2) declare one variable as @SearchString VARCHAR(8000)
3) Expected out

--
Chandu

sanjeevka08
Starting Member

18 Posts

Posted - 2014-05-15 : 02:50:18

Consider table Column Words

Words
tea
movie
art
think
film
theatre
outside
launch

mainstring varchar(8000)= i amgoingtoatheatre towatcham ovie.

Output must be

theatre
movie

Sanjeev Kumar

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-15 : 06:11:32

DECLARE @Table TABLE(Words varchaR(100))
insert @Table
SELECT 'tea' union all
SELECT 'movie' union all
SELECT 'art' union all
SELECT 'think' union all
SELECT 'film' union all
SELECT 'theatre' union all
SELECT 'outside' union all
SELECT 'launch'

DECLARE @mainstring varchar(8000)= 'i amgoingtoatheatre towatcham ovie'

--Output must be

--theatre
--movie

SELECT * FROM @Table
WHERE LEN(REPLACE ( REPLACE(@mainstring, ' ', ''), Words, ''))< LEN(REPLACE(@mainstring, ' ', ''))

--
Chandu

sanjeevka08
Starting Member

18 Posts

Posted - 2014-05-15 : 06:26:20

Thanks Chandhu

But is it possible to perform this if Table contain data as Movieshow, but we need result as movie. i mean something using like operator

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-15 : 07:31:03

May be this will help you ......


CREATE TAble TestIN(Col VARCHAR(MAX))
INSERT INTO TestIN VALUES('Words'),('tea'),('movie'),('art'),('think'),('film'),('theatre'),('outside'),('launch'),('movieshow')

-----SELECT * FROM TestIN
-----You can specify your selection criteria in below where clause------
DECLARE @SearchString varchar(8000)= REPLACE('i amgoingtoatheatre towatcham ovie.',' ','')
DECLARE @MaxCount INT = (SELECT COUNT(*) FROM TestIN)
DECLARE @Table  TABLE(Val VARCHAR(MAX))
DECLARE @Start INT =1
WHILE  (@Start <=@MaxCount)
BEGIN
	DECLARE @SearchOption VARCHAR(1024)=(SELECT Col FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS Rn FROM TestIN WHERE Col NOT LIKE 'movieshow' )a WHERE  Rn =@Start ) 
	INSERT INTO @Table 
	SELECT 
	CASE WHEN PATINDEX('%'+@SearchOption+'%',@SearchString)<>0 THEN SUBSTRING(@SearchString,PATINDEX('%'+@SearchOption+'%',@SearchString),LEN(@SearchOption))
	END Result
	SET @Start = @Start+1
END
SELECT * FROM @Table WHERE Val IS NOT NULL

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-15 : 07:36:18

quote:
Originally posted by sanjeevka08

Thanks Chandhu

But is it possible to perform this if Table contain data as Movieshow, but we need result as movie. i mean something using like operator

(For chandu's solution)Have you tried this way


SELECT * FROM @Table
WHERE LEN(REPLACE ( REPLACE(@mainstring, ' ', ''), Words, ''))< LEN(REPLACE(@mainstring, ' ', '')) AND Words NOT LIKE 'movieshow'

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

sanjeevka08
Starting Member

18 Posts

Posted - 2014-05-15 : 07:54:55

Thanks

But this is not what I mean. Consider the below requirement

Consider table Column Words

Words
tea
movieshow
art
think
film
theatre
outside
launch

mainstring varchar(8000)= i amgoingtoatheatre towatcham ovie.

Output must be

theatre
movie

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-15 : 08:13:12

Hi Sanjeev try this logic ...It's worked fine for me Check it out...



CREATE TAble TestIN(Words VARCHAR(MAX))
INSERT INTO TestIN VALUES('tea'),('movie'),('art'),('think'),('film'),('theatre'),('outside'),('launch'),('movieshow')

-----SELECT * FROM TestIN
-----You can specify your selection criteria in below where clause------
DECLARE @SearchString varchar(8000)= REPLACE('i amgoingtoatheatre towatcham ovie.',' ','')
DECLARE @MaxCount INT = (SELECT COUNT(*) FROM TestIN)
DECLARE @Table  TABLE(Val VARCHAR(MAX))
DECLARE @Start INT =1
WHILE  (@Start <=@MaxCount)
BEGIN
	DECLARE @SearchOption VARCHAR(1024)=(SELECT Words FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS Rn FROM TestIN WHERE Words IN('theatre','movie'))a WHERE  Rn =@Start ) 
	INSERT INTO @Table 
	SELECT 
	CASE WHEN PATINDEX('%'+@SearchOption+'%',@SearchString)<>0 THEN SUBSTRING(@SearchString,PATINDEX('%'+@SearchOption+'%',@SearchString),LEN(@SearchOption))
	END Result
	SET @Start = @Start+1
END
SELECT * FROM @Table WHERE Val IS NOT NULL

DROP TABLE TestIN

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

sanjeevka08
Starting Member

18 Posts

Posted - 2014-05-15 : 08:21:11

Thanks.

But it take too much time for my actual table with 85000 records

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-15 : 08:43:29

Sorry for that...While Loop takes more execution time..I changed entire query...try this...


DECLARE @Table TABLE(Words varchaR(100))
insert @Table
SELECT 'tea' union all
SELECT 'movie' union all
SELECT 'art' union all
SELECT 'think' union all
SELECT 'film' union all
SELECT 'theatre' union all
SELECT 'outside' union all
SELECT 'launch'  UNION ALL
SELECT 'movieshow'


DECLARE @SearchString varchar(8000)= REPLACE('i amgoingtoatheatreti towatcham ovieshow now theatre',' ','')
SELECT * FROM 
(SELECT 
	CASE WHEN PATINDEX('%'+Words+'%',@SearchString)<>0 THEN SUBSTRING(@SearchString,PATINDEX('%'+Words+'%',@SearchString),LEN(Words))
	END Result
FROM 
@Table ) a WHERE Result IS NOT NULL AND Result IN('theatre','movie')

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-05-15 : 10:21:31

Did you even run what Chandu posted? works for me no problem.




DECLARE @Table TABLE(Words varchaR(100))
insert @Table
SELECT 'tea' union all
SELECT 'movieshow' union all  -- <<< your other requirement
SELECT 'movie' union all
SELECT 'art' union all
SELECT 'think' union all
SELECT 'film' union all
SELECT 'theatre' union all
SELECT 'outside' union all
SELECT 'launch'

DECLARE @mainstring varchar(8000)= 'i amgoingtoatheatre towatcham ovie'

SELECT * FROM @Table
WHERE LEN(REPLACE ( REPLACE(@mainstring, ' ', ''), Words, ''))< LEN(REPLACE(@mainstring, ' ', ''))

OUTPUT:
Words
movie
theatre

Looks exactly like you asked.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sanjeevka08
Starting Member

18 Posts

Posted - 2014-05-16 : 05:37:12

Hi DonAtWork,

Chandu's soultion you have tested having both movie and movieshow in the union condition. Thats why it produced correct output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources