Equiv to VBA's Instr function for SQL?

Equiv to VBA's Instr function for SQL?

Post by mark » Wed, 20 Nov 2002 16:27:39



Hi,

Just wondering if there were an equivalent to VBA's
function InStr?

This function basically determines if there is a string
contained within another string, very useful and required
by many of my access views which are going to be converted
to views and stored procedures so I'm just looking for
equivalent code on the SQL Side to accomplish this.  I'm
not really seeing the same types of examples in
Developer's handbooks for SQL Server as I am for
developer's handbooks for Access and VBA which are much
more useful so it's kind of dissapointing.  Is SQL really
so limited in what it can do or has nobody gone through
the hastle to take all of the access and vba code common
to access queries and dump it into SQL views and stored
procedures?  Not that I won't figure this out but it's
alot easier if someone else has already done it and I can
use their code!  Any help for resources on this dilemma
would be much appreciated.

InStr(Start,String1,String2,Compare as vbCompareMethod =
vbCompareBinary)

So I'd use it in this manner

If Instr(MyString,"Hey", " ") <> 0 Then
   DOWHATEVER
End if

Where MyString = "Hey how's it goin?"

so the above statement sees "Hey" in MyString and returns
a boolean value of whatever (not 0) which = true.

Thanx in advance for your help.

Mark.

 
 
 

Equiv to VBA's Instr function for SQL?

Post by Tibor Karasz » Wed, 20 Nov 2002 16:54:14


You might be able to use CHARINDEX or PATINDEX for that.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:> Hi,

> Just wondering if there were an equivalent to VBA's
> function InStr?

> This function basically determines if there is a string
> contained within another string, very useful and required
> by many of my access views which are going to be converted
> to views and stored procedures so I'm just looking for
> equivalent code on the SQL Side to accomplish this.  I'm
> not really seeing the same types of examples in
> Developer's handbooks for SQL Server as I am for
> developer's handbooks for Access and VBA which are much
> more useful so it's kind of dissapointing.  Is SQL really
> so limited in what it can do or has nobody gone through
> the hastle to take all of the access and vba code common
> to access queries and dump it into SQL views and stored
> procedures?  Not that I won't figure this out but it's
> alot easier if someone else has already done it and I can
> use their code!  Any help for resources on this dilemma
> would be much appreciated.

> InStr(Start,String1,String2,Compare as vbCompareMethod =
> vbCompareBinary)

> So I'd use it in this manner

> If Instr(MyString,"Hey", " ") <> 0 Then
>    DOWHATEVER
> End if

> Where MyString = "Hey how's it goin?"

> so the above statement sees "Hey" in MyString and returns
> a boolean value of whatever (not 0) which = true.

> Thanx in advance for your help.

> Mark.


 
 
 

1. Need T-SQL Equiv to VBA InStr() Function

So far, I've only located the SUBSTRING function, which could
be used to step through a string looking for the character I need,
but that seems more difficult than it should be.

Any suggestions on T-SQL InStr() function?

--

Danny J. Lesandrini

www.dea.com/datafast

2. problem of ODBC via VPN

3. T-SQL equiv to 'BREAK ON'

4. Newbie Questions About DAO Queries to SQL Server

5. INSTR('/')- SQL problem

6. CLOB serach

7. SQL equiv of the MID function

8. equivalent of instr function in SQL server 7?

9. (SQL Server) VB InStr(...) Function Lookalike

10. T-SQL verion of Instr function

11. instr() not functioning in SQL query

12. Proble with INSTR function in SQL statement