Query to select data based on alphanumeric (surname) information.

Hello,

I am trying to write a query that will be able to select different segments of data based on spelling of the last name.

For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'

then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...

I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?

Thanks

It isn't perfectly straightforward, particularly at the end of the ranges, but this will work...

create table person

(

lastName varchar(20)

)

insert into person

select 'AAA Dude'

union all

select 'Branson'

union all

select 'EJJ Dude'

union all

select 'Flighter'

union all

select 'Jaenor'

union all

select 'Zoinks'

--first

select *

from person

where lastName >= 'a' --Just one letter needed here because we want all

and lastName < 'EJK' --Added one letter to the range you want

lastName

--

AAA Dude

Branson

EJJ Dude

select *

from person

where lastName >= 'EJK'

and lastName < 'JAF' --again, one character more than the range

lastName

--

Flighter

Jaenor

select *

from person

where lastName >= 'JAF'

and lastName <= replicate('Z',20) --All zzz's would be the end of the range

--replicated to the max length of the column

lastName

--

Zoinks

|||

The following querry may fit for you,

Code Snippet

Create Table #person (

[lastName] Varchar(100)

);

Insert Into #person Values('AAA Dude');

Insert Into #person Values('Branson');

Insert Into #person Values('EJJ Dude');

Insert Into #person Values('Flighter');

Insert Into #person Values('Jaenor');

Insert Into #person Values('Zoinks');

Insert Into #person Values('EJK');

Insert Into #person Values('EJ');

Insert Into #person Values('EJJ');

Insert Into #person Values('EJJ ZZZZZZ');

Insert Into #person Values('JA');

Insert Into #person Values('JAE');

Select * from #person

Where

[lastName] >= 'AAA'

And [lastName] <= 'EJJ' + Replicate(Char(255),100) -- change the length with your datatype

Select * from #person

Where

[lastName] >= 'EJK'

And [lastName] <= 'JAE' + Replicate(Char(255),100) -- change the length with your datatype

|||

The reason I went with:

where lastName >= 'a' --Just one letter needed here because we want all

Was that I wanted to make sure that all alpha numeric values got in, even if the value 'A boinger' was in the table. If there are no spaces in the code value, it wouldn't matter. For example:

select case when 'a boinger' >= 'AAA' then 'yes' else 'no' end

Would be 'no' since 'a b' < 'AAA'

|||

Having worked with large criminal justice applications/databases, I've noticed that often when arrested, folks may have 'odd' street names, or the arresting officer is dealing with someone unconscious or intoxicated, so the name in the database may be '10 minute Fred', '2 Drink Limit', '1perp', '2ndDrunk', etc.

So, I would not limit names to starting with alpha...

('10 minute Fred' was never in a house/business for more than 10 minutes when he was burglurizing them.)