Instring command - 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

Instring command

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-08-24 : 05:32:31

Hi friends,

Is there an instring command in SQL like there is in VB?

I have a string passed through to a stored procedure as follows "bungalow, flat, farm, cottage, studio"

The database contains a field which will relate to one of these.

The data I am trying to pull through is anything that is listed in that string.

I was playing with:

select * from tb_properties where prop_type like '%' + @typestring + '%'

Then I realised that is completely wrong, any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:04:26

select * from tb_properties where '%,' + @typestring + ',%' like ','+cast(prop_type varchar(10))+','

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:24:59

shouldnt it be?

select * from tb_properties where ',' + @typestring + ',' like '%,'+cast(prop_type varchar(10))+',%'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:25:28

Also equivalent of Instr in t-sql is PATINDEX

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 08:22:23

quote:
Originally posted by visakh16

shouldnt it be?

select * from tb_properties where ',' + @typestring + ',' like '%,'+cast(prop_type varchar(10))+',%'

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

You are correct

Madhivanan

Failing to plan is Planning to fail

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-08-24 : 08:23:18

many thanks for your reply.

Unfortunately this still returns nothing as the comparison is only between the item inbetween the first set of commas. As per the example - this is only checking against bungalow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 08:26:37

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-08-24 : 09:28:25

quote:
Originally posted by visakh16

Also equivalent of Instr in t-sql is PATINDEX

I think CHARINDEX would be a closer work-alike to INSTR ??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 10:02:12

quote:
Originally posted by Kristen

quote:
Originally posted by visakh16

Also equivalent of Instr in t-sql is PATINDEX

I think CHARINDEX would be a closer work-alike to INSTR ??

depends on whether you're looking for character or pattern

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

Kristen
Test

22859 Posts

Posted - 2010-08-24 : 13:17:22

I didn't think that INSTR did a pattern - but I haven't used it in a while.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources