Searching in a string - 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

General SQL Server Forums

New to SQL Server Programming

Searching in a string

Author

Topic

pureclass85
Starting Member

29 Posts

Posted - 2009-03-08 : 11:40:29

i am looking for an SQL operator that allows you to match part of a string

i.e

string ..... "bob sat on a mat"

where part(string) = sat

return results

p.s i know it is bad psdocode lol

thanks guys

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-08 : 11:59:58

[code]where string like '%sat%'[/code]

pureclass85
Starting Member

29 Posts

Posted - 2009-03-08 : 12:03:12

chears thanks but just figured it out lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 12:42:20

also WHERE PATINDEX('%sat%',string)>0 if column doesnt have an index on it

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-09 : 05:17:37

quote:
Originally posted by visakh16

also WHERE PATINDEX('%sat%',string)>0 if column doesnt have an index on it

'%string%' also wont make use of index

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 02:28:12

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

also WHERE PATINDEX('%sat%',string)>0 if column doesnt have an index on it

'%string%' also wont make use of index

Madhivanan

Failing to plan is Planning to fail

yup but wont this make use of index?

where string like '%sat%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-10 : 02:47:37

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

also WHERE PATINDEX('%sat%',string)>0 if column doesnt have an index on it

'%string%' also wont make use of index

Madhivanan

Failing to plan is Planning to fail

yup but wont this make use of index?

where string like '%sat%'

It causes table scan than seek. See the execution plan

declare @test table(names varchar(100) primary key clustered)
insert into @test
select 'test1' union all
select 'test2' union all
select 'test3' union all
select 'test4' union all
select 'test5' union all
select 'test6'
select * from @test where names like 'test%'
select * from @test where names like '%test%'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 02:50:10

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

also WHERE PATINDEX('%sat%',string)>0 if column doesnt have an index on it

'%string%' also wont make use of index

Madhivanan

Failing to plan is Planning to fail

yup but wont this make use of index?

where string like '%sat%'

It causes table scan then seek. See the execution plan

declare @test table(names varchar(100) primary key clustered)
insert into @test
select 'test1' union all
select 'test2' union all
select 'test3' union all
select 'test4' union all
select 'test5' union all
select 'test6'
select * from @test where names like 'test%'
select * from @test where names like '%test%'

Madhivanan

Failing to plan is Planning to fail

yup...that was the initial suggestion...thats why i told if it has indexd use first else you can use mine too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources