SQL to restrict rows by first letter / number

[DimPrawn]

Thicky question.

Say you have names for bands and you want to display all that begin with a specific letter.

# A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Easy, you can:

SELECT band_name
FROM bands
WHERE band_name LIKE 'A%'

But what if you want to have # select all bands that don't begin with A - Z ( e.g. they begin with a number).

such as 22 Jacks, or 1080?

Using one piece of SQL to handle the trivial A-Z and the # option without going mental silly?

[TheBigYinJames]

Originally Posted by DimPrawn

Thicky question.
Using one piece of SQL to handle the trivial A-Z and the # option without going mental silly?

Can't see how you can do it without a big load of ORs or a temp table.

[Moscow Mule]

Can you do something like

Select * from bands where subst(band_name:1:1) not exist('ABCDECFGHIJKLMNOPQRSTUVWXYZ')

Syntax is wrong, but you get the idea...

[DimPrawn]

The SQL will have one paramater, the first letter (A-Z) or # passed in, where hash means 'not A-Z'.

Don't want to make something eloborate and some smarty pants laughs at me.

[expat]

Originally Posted by DimPrawn

Thicky question.

Say you have names for bands and you want to display all that begin with a specific letter.

# A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Easy, you can:

SELECT band_name
FROM bands
WHERE band_name LIKE 'A%'

But what if you want to have # select all bands that don't begin with A - Z ( e.g. they begin with a number).

such as 22 Jacks, or 1080?

Using one piece of SQL to handle the trivial A-Z and the # option without going mental silly?

SELECT band_name
FROM bands
WHERE UPPER(substr(band_name,1,1)) < 'A'
OR UPPER(substr(band_name,1,1)) > 'Z'

This uses Oracle's UPPER(string) to convert to upper case, your flavour of SQL will doubtless have something like it (I presume that you want a case-independent test).

[DimPrawn]

Originally Posted by expat

SELECT band_name
FROM bands
WHERE UPPER(substr(band_name,1,1)) < 'A'
OR UPPER(substr(band_name,1,1)) > 'Z'

This uses Oracle's UPPER(string) to convert to upper case, your flavour of SQL will doubtless have something like it (I presume that you want a case-independent test).

Great but you haven't read the requirement have you?

There is one parameter, either the letter I want or everything that does not start with a letter (# passed in) in one simple, efficient, fast SQL statement.

I know SQL very well and it's pretty obvious how to select everything that's not A to Z.

[rsingh]

Assuming that you pass in @P and have validated it, then something like the following should work.

SELECT band_name
FROM bands
WHERE 
(@P = '#' AND LEFT(band_name,1) NOT LIKE [a-z])
OR 
(@P != '#'  AND LEFT(band_name,1) = @P)

[DimPrawn]

Originally Posted by rsingh

Assuming that you pass in @P and have validated it, then something like the following should work.

SELECT band_name
FROM bands
WHERE 
(@P = '#' AND LEFT(band_name,1) NOT LIKE [a-z])
OR 
(@P != '#'  AND LEFT(band_name,1) = @P)

Now that's what I'm talking about!

[Moscow Mule]

Originally Posted by DimPrawn

I know SQL very well

Originally Posted by DimPrawn

Now that's what I'm talking about!

But you can't do an OR?

[DimPrawn]

Originally Posted by Moscow Mule

But you can't do an OR?

That's in module 2, to be learned next week.