how to use like - 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 2005 Forums

Transact-SQL (2005)

how to use like

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-10-03 : 11:39:28

Hi,

I need to use like operator to search, five characters in middle than some value after 5 characters. Please see the output value

Declare @tbl1 table
(ID INT,
Col1 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'Abc 12345 1999 mno'

INSERT INTO @tbl1
SELECT 2,'Abc 12 2001 xxx'

INSERT INTO @tbl1
SELECT 3,'Abc 12345 2002 ddxd'

INSERT INTO @tbl1
SELECT 4,'Mnop 12345 1999 ksks'

INSERT INTO @tbl1
SELECT 5,'Abc xyz 1999'

Output looks like this
select * from @tbl1 where col1 like '%Abc [five characters] 1999%'

ID Col1
1 Abc 12345 1999 mno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 12:00:57

select * from @tbl1 where col1 like '%Abc_____1999%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-10-03 : 12:06:26

how about the 5 words instate of letters?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 12:58:54

five words separated by space you mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-10-03 : 13:08:07

yes please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:30:28

[code]
SELECT t.ID,t.Col1
FROM @Tbl t
CROSS APPLY dbo.ParseValues(t.Col1,' ')f
GROUP BY t.ID,t.Col1
HAVING MAX(CASE WHEN f.ID=1 THEN f.Val ELSE NULL END) = 'Abc'
AND MAX(CASE WHEN f.ID=6 THEN f.Val ELSE NULL END) = '1999'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:31:30

Also code for ParseValues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-10-03 : 14:00:31

It took 22 minutes for 5 documents still not finish...i created parsevalues function as well...it does not work for 100k records.

Kristen
Test

22859 Posts

Posted - 2011-10-03 : 14:05:53

If you want performance don't store your data like that, parse it into the constituent parts so that you have structured data and then query will be instance.

You are trying to do the equivalent of using a phone directory to find people by the second line of their address ...

If its a one-off query live with the fact that it takes 20 minutes. Otherwise improve the structure of the data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 14:13:26

quote:
Originally posted by rudba

It took 22 minutes for 5 documents still not finish...i created parsevalues function as well...it does not work for 100k records.

why not try full text index then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources