Prev: Weekending - grouping by the week
Next: Calculate in SELECT
From: Craig on 18 Mar 2010 16:06
I'm using SQL2008. I'm searching postal addresses ,which I realize is a
rather difficult and obtuse task, but anyway...

Is there a way to search in TSQL using the "Like" operator that includes a
wildcard, in my case, a period? For instance, I'm searching for all
addresses that end in "Ave" (for Avenue). But I'm running into issues like
the following:

Syntax: Like '%ave%'
Returns: "111 Smith Ave"; "111 Smith Ave." (But it also undesirably returns
"111 Avenida Playa")

Syntax: Like '% ave %' (note the spaces...)
Returns: "111 Smith Ave" (But doesn't return "111 Smith Ave." (note the
period)


So, question: Can I construct something like this: Like '% Ave[.] %' that
picks up the period if it exists, and ignores it if it doesn't?

Sql doesn't seem to like that, though, as I get a syntax error.

Thanks for the help.
--
Craig
From: Tom on 18 Mar 2010 16:42
On Mar 18, 4:06 pm, Craig <Cr...(a)discussions.microsoft.com> wrote:
> I'm using SQL2008.  I'm searching postal addresses ,which I realize is a
> rather difficult and obtuse task, but anyway...
>
> Is there a way to search in TSQL using the "Like" operator that includes a
> wildcard, in my case, a period?  For instance, I'm searching for all
> addresses that end in "Ave" (for Avenue).  But I'm running into issues like
> the following:
>
> Syntax:  Like '%ave%'
> Returns:  "111 Smith Ave"; "111 Smith Ave." (But it also undesirably returns
>  "111 Avenida Playa")
>
> Syntax:  Like '% ave %'  (note the spaces...)
> Returns:  "111 Smith Ave" (But doesn't return "111 Smith Ave."  (note the
> period)
>
> So, question:  Can I construct something like this:  Like '% Ave[.] %'  that
> picks up the period if it exists, and ignores it if it doesn't?
>
> Sql doesn't seem to like that, though, as I get a syntax error.
>
> Thanks for the help.
> --
> Craig


What error are you getting? The following works for me.

like '%[ ]ave[ .]%'
From: Craig on 18 Mar 2010 20:52
Well, that was EXACTLY what I was looking for. Thank you very much, Tom.

PS: The error was just "syntax error near the word like"....
--
Craig


"Tom" wrote:

> On Mar 18, 4:06 pm, Craig <Cr...(a)discussions.microsoft.com> wrote:
> > I'm using SQL2008. I'm searching postal addresses ,which I realize is a
> > rather difficult and obtuse task, but anyway...
> >
> > Is there a way to search in TSQL using the "Like" operator that includes a
> > wildcard, in my case, a period? For instance, I'm searching for all
> > addresses that end in "Ave" (for Avenue). But I'm running into issues like
> > the following:
> >
> > Syntax: Like '%ave%'
> > Returns: "111 Smith Ave"; "111 Smith Ave." (But it also undesirably returns
> > "111 Avenida Playa")
> >
> > Syntax: Like '% ave %' (note the spaces...)
> > Returns: "111 Smith Ave" (But doesn't return "111 Smith Ave." (note the
> > period)
> >
> > So, question: Can I construct something like this: Like '% Ave[.] %' that
> > picks up the period if it exists, and ignores it if it doesn't?
> >
> > Sql doesn't seem to like that, though, as I get a syntax error.
> >
> > Thanks for the help.
> > --
> > Craig
>
>
> What error are you getting? The following works for me.
>
> like '%[ ]ave[ .]%'
> .
>
 | 
Pages: 1
Prev: Weekending - grouping by the week
Next: Calculate in SELECT