Post reply

Hyphen vs. Underscore in LIKE clause? why they are the same ??

  • November 27, 2013 at 4:06 pm

    #286874

    Can't understand why it happening, am I right in my sample, or there is any special preset option I'm using why they work the same in LIKE:

    ;with t as (

    select 'alpha-bravo hyphen ' c1 union

    select 'alpha_bravo underscore' c1 )

    select * from t where c1 like 'alpha_bravo%'

    --select * from t where substring(c1,6,1) = '-'

    --select * from t where substring(c1,6,1) = '_'

    And it works fine with substring.

    Best to all

    M

  • November 27, 2013 at 5:05 pm

    #1669597

    Never mind!! I g_t !

    Best to all

    V

  • November 28, 2013 at 9:24 am

    #1669701

    Just to make it clear for someone else.

    The underscore acts as a single character wildcard with the LIKE operator. To ensure you get an underscore, you need to put it between brackets([]) or use an escape character.

    Example:

    --This returns all rows

    with t as (

    select 'alpha-bravo hyphen ' c1 union all

    select 'alpha_bravo underscore' c1 union all

    select 'alpha&bravo underscore' c1 union all

    select 'alphacbravo underscore' c1 )

    select * from t where c1 like 'alpha_bravo%';

    --This returns only the one with underscore

    with t as (

    select 'alpha-bravo hyphen ' c1 union all

    select 'alpha_bravo underscore' c1 union all

    select 'alpha&bravo underscore' c1 union all

    select 'alphacbravo underscore' c1 )

    select * from t where c1 like 'alpha[_]bravo%';

    --Using ESCAPE character. \ can be changed to any character.

    with t as (

    select 'alpha-bravo hyphen ' c1 union all

    select 'alpha_bravo underscore' c1 union all

    select 'alpha&bravo underscore' c1 union all

    select 'alphacbravo underscore' c1 )

    select * from t where c1 like 'alpha\_bravo%' ESCAPE '\';

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply