selecting values where the value is between two strings in SQL

Question

I'm trying to run a query that selects an id that's between two strings. the query I'm using is

select netid from ALL_GROUP_MEMBERS_VIEW where netid >'aa%' and netid < 'b%' and
      gid='213' order by netid

This obviously isn't working, but I'm not sure how to get those values. Netid is a unique identifier for an individual.

Could you post some sample data and the result you're trying to get? — Mureinik
– Mureinik, Commented Jul 17, 2014 at 16:22
Put sample data in text, and the result you wish to achieve so that the others may better help you. — Will Marcouiller
– Will Marcouiller, Commented Jul 17, 2014 at 16:27
Sorry, that was a typo. I meant to post "Can't post data because the table contains sensitive information". Mureinik's answer below fixed my issue. Once I took the wildcard out, it worked. — jordaniac89
– jordaniac89, Commented Jul 17, 2014 at 18:28

Mureinik · Accepted Answer · 2014-07-17 16:30:16Z

1

The % character is a wildcard for the LIKE operator, and has no function (other than as a literal) in string comparison operations. If you remove it, you should get the right behavior:

SELECT   netid
FROM     all_group_members_view
WHERE    netid > 'aa' AND netid < 'b' AND gid = '213'
ORDER BY netid

To make this query more elegant, you can replace the pair of > and < operators with a single between operator:

SELECT   netid
FROM     all_group_members_view
WHERE    netid BETWEEN 'aa' AND 'b' AND gid = '213'
ORDER BY netid

answered Jul 17, 2014 at 16:30

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jordaniac89 Over a year ago

Thanks. Taking the wildcard out fixed it.

Will Marcouiller · Accepted Answer · 2014-07-17 16:30:18Z

0

You can try this:

select netid 
    from ALL_GROUP_MEMBERS_VIEW 
    where substr(netid,1,1) between 'A' and 'B' 
        and gid='213' 
    order by netid

edited Jul 17, 2014 at 16:30

Will Marcouiller

24.3k26 gold badges100 silver badges166 bronze badges

answered Jul 17, 2014 at 16:27

David Stetler

1,46110 silver badges14 bronze badges

Collectives™ on Stack Overflow

selecting values where the value is between two strings in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related