find a character in string

9 Sep 2005 3:49 PM

Rose

Good morning,

In the past, with an Informix db and asp/vbscript pages, I've used the
following SQL statement:

select CustomerName, DrawNumber
from Customer
Where DrawNumber[4,4] not in ("I","M")

to find DrawNumber where the 4th character is not equal to I or M. Now that
we've switched over to a SQLServer db, I'm having some trouble getting this
statement work, or finding the equivalent. Any suggestions?

Thanks in advance!

Rose

9 Sep 2005 3:55 PM

Anith Sen

Lookup how to use SUBSTRING() function in SQL Server Books Online.

--
Anith

9 Sep 2005 4:01 PM

Perayu

Look at SUBSTRING() in BOL. It will help.

Perayu

Show quote

"Rose" <R***@discussions.microsoft.com> wrote in message
news:BA7A20AD-3FF6-414D-96AC-8DF5C34FF75B@microsoft.com...
> Good morning,
>
> In the past, with an Informix db and asp/vbscript pages, I've used the
> following SQL statement:
>
> select CustomerName, DrawNumber
> from Customer
> Where DrawNumber[4,4] not in ("I","M")
>
> to find DrawNumber where the 4th character is not equal to I or M. Now
> that
> we've switched over to a SQLServer db, I'm having some trouble getting
> this
> statement work, or finding the equivalent. Any suggestions?
>
> Thanks in advance!
>
> Rose

9 Sep 2005 4:03 PM

Trey Walpole

where DrawNumber not like '___[IM]%'

(3 underscores before the opening bracket)

Rose wrote:
Show quote

> Good morning,
>
> In the past, with an Informix db and asp/vbscript pages, I've used the
> following SQL statement:
>
> select CustomerName, DrawNumber
> from Customer
> Where DrawNumber[4,4] not in ("I","M")
>
> to find DrawNumber where the 4th character is not equal to I or M. Now that
> we've switched over to a SQLServer db, I'm having some trouble getting this
> statement work, or finding the equivalent. Any suggestions?
>
> Thanks in advance!
>
> Rose

9 Sep 2005 4:27 PM

Rose

Thanks Trey! It worked perfectly!

Show quote

"Trey Walpole" wrote:

> where DrawNumber not like '___[IM]%'
>
> (3 underscores before the opening bracket)
>
> Rose wrote:
> > Good morning,
> >
> > In the past, with an Informix db and asp/vbscript pages, I've used the
> > following SQL statement:
> >
> > select CustomerName, DrawNumber
> > from Customer
> > Where DrawNumber[4,4] not in ("I","M")
> >
> > to find DrawNumber where the 4th character is not equal to I or M. Now that
> > we've switched over to a SQLServer db, I'm having some trouble getting this
> > statement work, or finding the equivalent. Any suggestions?
> >
> > Thanks in advance!
> >
> > Rose
>